ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
2 Day Countdown - The Alteryx Community will be temporarily unavailable for a few hours due to scheduled maintenance starting on Thursday, April 22nd at 5pm MDT. Please plan accordingly.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Case statement with an OVER condition

BonusCup
8 - Asteroid

Hi,

 

Our company currently uses Spotfire as our visualization tool but we are in the process of moving to Tableau.  I have one dashboard that has multiple calculated columns like the below.

 

Sum(case when ([DateDiff] in (0)) and ([Source]="Mkt Src") and ([SIGNAL]="Cable") then [Mins] end) OVER ([ID])

 

COLLECTIONDATEIDSourceSIGNALCurrentDataDateMins
2/9/202112304Mkt SrcDBS2/9/20210
2/9/202132013Mkt SrcDBS2/9/20210
2/9/202113355Mkt SrcCable2/9/20218.02
2/9/202136919Mkt SrcMDS2/9/20211.28
2/9/202112783Mkt SrcDBS2/9/20210.23
2/9/202120878Mkt SrcMDS2/9/20210
2/9/20214097Mkt SrcCable2/9/20216.25
2/9/202112783Mkt SrcCable2/9/20210
2/9/202116355Mkt SrcCable2/9/20215.48
2/9/202118384Mkt SrcDBS2/9/20210
2/9/202114847Mkt SrcDBS2/9/20210
2/9/202121333Mkt SrcDBS2/9/202111.53
2/9/202112868Mkt SrcDBS2/9/20210
2/9/202110487Mkt SrcDBS2/9/20210
2/9/202148682Mkt SrcMDS2/9/20210
2/9/202112304Mkt SrcDBS2/9/20210
2/9/202110514Mkt SrcCable2/9/20210
2/9/202118384Mkt SrcDBS2/9/20210
2/9/202120223Mkt SrcDBS2/9/20210.33
2/9/202114408Mkt SrcCable2/9/20210
2/9/202110508Mkt SrcDBS2/9/20212.77
2/9/202118384Mkt SrcDBS2/9/20210
2/9/202114914Mkt SrcDBS2/9/20212.52
2/9/20214021Mkt SrcCable2/9/20210.03
2/9/20214021Mkt SrcCable2/9/20210

 

With over 200 calculated columns similar to this one Spotfire was extremely slow to load so we would like to leverage Alteryx to do all of these calculations.  I've looked through the Community and online but was unable to figure out how to convert this to an Alteryx formula.  Appreciate any help with this.

 

Thanks

afv2688
16 - Nebula
16 - Nebula

Hello @BonusCup ,

 

You can for sure perform this calculation on alteryx, it depends on how the data comes in. If you could mock up an entry with the necessary columns for the calculation I may be able to establish something for you. As I said, the data doesnt need to be real, just need to see the input format. I'm telling you this because I'm not sure if what you are showing is the output or input.

 

If there are other calculations that share the same columns and differ a little bit from one anothe, like signal =DBS instead of cable, I may be able to establish something even better to aviod coding as much as possible.

 

Regards

BonusCup
8 - Asteroid

@afv2688 

 

Thanks for the quick response.  Attached is a spreadsheet with some sample input data I am using.  Your second point about other calculations that share the same columns and differ a little bit from one another is accurate.  I added a "Formulas" sheet showing some of the calculations I used for Spotfire.

 

BonusCup_0-1613057791268.png

 

Let me know if anything else is needed.  Thanks again

 

BonusCup
8 - Asteroid

@afv2688 

 

I failed to include what I used for DateDiff in Alteryx

 

Spotfire:  DateDiff("day",[METERCOLLECTIONDATE],[CurrentDataDate])

Alteryx:  DateTimeDiff([METERCOLLECTIONDATE],[CurrentDataDate],'days')

afv2688
16 - Nebula
16 - Nebula

Hello @BonusCup ,

 

I have created a little worfklow in which you can see how to perform the calculations for the first 3 columns,

 

I may come up with something more, need a little time to think 😉 quite busy right now!

 

Regards

afv2688
16 - Nebula
16 - Nebula

Hello @BonusCup ,

 

added a few more. Hope it helps

 

Regards

BonusCup
8 - Asteroid

@afv2688 

 

Thanks.  Your 2nd one is very helpful.  I wasn't sure about the ABS DateTimeDiff formula in the first one you shared.  I'm digging into your 2nd one and wasn't sure about the dateDiff in that one also:  DateTimeDiff(DateTimeStart(),[METERCOLLECTIONDATE],'days')

 

I'm trying to compare the most current METERCOLLECTIONDATE, in this case 2021-02-09, which would be day 0, to the previous 3 and 7 days.  I should've let you know that the data comes in with 10 days worth of data.  Should I use the Summarize tool and create a new 'CurrentDataDate' field to get the Max METERCOLLECTIONDATE and then for the dateDiff use DateTimeDiff([METERCOLLECTIONDATE],[CurrentDataDate],'days')?

afv2688
16 - Nebula
16 - Nebula

Hello @BonusCup,

 

What you are proposing could be a good solution for what you are trying to get using the summarize tool you could definetly get the max date and then use the append tool to have it added to the table. Would work perfecty 🙂

 

If there are any other questions please let me know!

 

Will try to answer as soon as possible

 

Regards

BonusCup
8 - Asteroid

@afv2688 

 

Thanks again.  With you suggestions I was able to figure out the logic I needed.

 

 

Labels