Celia, you can write the calc this way:
IF MAX(DATEPART('month', [Month of Week])) <> 12 THEN SUM([Contract Revenue]) ELSE SUM([Contract Revenue]) + WINDOW_SUM(SUM([Diff.]), FIRST(), 0) END
And it will give you the same values as you have in column F in your Excel file. Is that what you are looking for? If not, what version of Tableau are you using. Thanks,
Really appreciated for your help!! It is exactly what I'm looking for and works well now.
I'm trying to understand your fix better, especially for the use of max(). It seems that datepart() generates non-aggregated arguments in IF statement. Max() can help convert the result of datepart() to aggregated argument and also point to 12 for this case.
What if contract revenue and cum diff. need to be summed up for both of Nov and Dec? Is there any other formula that can convert datapart () from non-aggregated to aggregated?
Please advise. Thank you!
1 of 1 people found this helpful
It would be something like this:
IF MAX(DATEPART('month', [Month of Week])) <= 11 THEN SUM([Contract Revenue]) ELSE SUM([Contract Revenue]) + WINDOW_SUM(SUM([Diff.]), FIRST(), 0) END
All I did was changed <> 12 to <= 12.
Yes MAX() is being used to aggregate the Month of Week field. But since you have the Month of Month of Week field in the viz, you could just as easily use MIN() as this will return the same result. This is because the Month of Month of Week is only going to return a single value. [ SUM() is the aggregate you need to stay away from as this would add up all the month parts for all the records.] You could also use ATTR() to do the aggregation if this is less confusing for you.
There's probably an LOD expression we could use to get rid of that WINDOW_SUM table calculation, but you didn't mention what version of Tableau you are using. LODs are only available in 9.x
Your reply is really helpful!!
My Tableau is 9.0. Thanks for letting me know the new LOD feature. I downloaded the whitepapers and learned its powerful functions. However, I get a more challenging task based on the above question and am stuck on only one thing with LOD. Hope you can help me with this:
Now, the revenue is counted on the Deal level. For each deal, the total revenue count should show the minimum b/w total Contract revenue and total Delivered revenue (meaning, we don't charge the total over-delivered part). But on a monthly basis we only recognize contract revenue and reconcile revenue numbers only for the last month of a deal. Compared to the above question, the cum difference b/t contract and delivered still needs to be added to the last month for each deal but based on one condition - whether the difference b/t SUM (Contract) and SUM(Delivered) is negative.
Here are two sample under-delivered deals (if over-delivered, it's easy to handle, just using contract revenue and ignoring the difference):
In the table, the Adjusted Revenue marked in Red is what I'm looking for. For deal D0453, we want to recognize total delivered revenue as 458,950,118. Since the first three months recognized contract revenues, the last month needed to reconcile the number by adding the total difference to June's contract count.
In Tableau, I created a formula as below. It didn't work because I figured that under deal there could be more than one lines carrying contract revenue counts. So if a deal has more than one line, the below query marked in Yellow means adding the total difference count to each line.
I can use custom SQL to sum all lines up first and then import it into Tableau. But curious if there is a way to directly fix this but keep all fields. Please advise. I have attached the Tableau workbook here.
Sample Workbook 2.twbx 26.4 KB
Thanks for the workbook! Celia I won't get to this until this weekend, so hopefully someone can help you out before then.
Thanks for letting me know. No rush. This weekend will be good. If you still couldn't get to this then, I will create a new post and hopefully someone else can help me out.