Case statement with an OVER condition

[BonusCup]

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])

COLLECTIONDATE	ID	Source	SIGNAL	CurrentDataDate	Mins
2/9/2021	12304	Mkt Src	DBS	2/9/2021	0
2/9/2021	32013	Mkt Src	DBS	2/9/2021	0
2/9/2021	13355	Mkt Src	Cable	2/9/2021	8.02
2/9/2021	36919	Mkt Src	MDS	2/9/2021	1.28
2/9/2021	12783	Mkt Src	DBS	2/9/2021	0.23
2/9/2021	20878	Mkt Src	MDS	2/9/2021	0
2/9/2021	4097	Mkt Src	Cable	2/9/2021	6.25
2/9/2021	12783	Mkt Src	Cable	2/9/2021	0
2/9/2021	16355	Mkt Src	Cable	2/9/2021	5.48
2/9/2021	18384	Mkt Src	DBS	2/9/2021	0
2/9/2021	14847	Mkt Src	DBS	2/9/2021	0
2/9/2021	21333	Mkt Src	DBS	2/9/2021	11.53
2/9/2021	12868	Mkt Src	DBS	2/9/2021	0
2/9/2021	10487	Mkt Src	DBS	2/9/2021	0
2/9/2021	48682	Mkt Src	MDS	2/9/2021	0
2/9/2021	12304	Mkt Src	DBS	2/9/2021	0
2/9/2021	10514	Mkt Src	Cable	2/9/2021	0
2/9/2021	18384	Mkt Src	DBS	2/9/2021	0
2/9/2021	20223	Mkt Src	DBS	2/9/2021	0.33
2/9/2021	14408	Mkt Src	Cable	2/9/2021	0
2/9/2021	10508	Mkt Src	DBS	2/9/2021	2.77
2/9/2021	18384	Mkt Src	DBS	2/9/2021	0
2/9/2021	14914	Mkt Src	DBS	2/9/2021	2.52
2/9/2021	4021	Mkt Src	Cable	2/9/2021	0.03
2/9/2021	4021	Mkt Src	Cable	2/9/2021	0

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]

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]

@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.

Let me know if anything else is needed.  Thanks again

[BonusCup]

@afv2688 

I failed to include what I used for DateDiff in Alteryx

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

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

[afv2688]

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 · Accepted solution]

Hello @BonusCup ,

added a few more. Hope it helps

Regards

[BonusCup]

@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]

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]

@afv2688 

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