LAC with If else

Question & Answer
, , ,
1

I am trying to create this calculated field and getting an error, all I am trying to do is getting Latest 3 months revenue, but revenue is calculated by summarizing max value for last 3 months. I do not want Calendar date in visual

ifelse (
truncDate(‘MM’,{calendar_date})>=truncDate(‘MM’,addDateTime(-2,‘MM’,{Last Month})) AND truncDate(‘MM’,{calendar_date}) <= truncDate(‘MM’,{Last Month}),

sum(max({total_aws_spend}, [{calendar_date}])),
0)

2

Hi @sarawgia,

Thanks for posting your question, to help can you confirm that the “Latest 3 months revenue” is equal to the sum of the maximum of the previous 3 months?
i.e. if today is the 1st July, is it the sum of the maximum of “total_aws_spend” for April, May and June?

Many Thanks,
Andrew

1 Like
3

Hi @abacon , that is correct

4

Hello @sarawgia, I think if simplify the ifelse statement above, you should be able to achieve your result by running the aggregation in the field well.

ifelse(
truncDate(‘MM’,{calendar_date})>=truncDate(‘MM’,addDateTime(-2,‘MM’,{Last Month})) AND truncDate(‘MM’,{calendar_date}) <= truncDate(‘MM’,{Last Month}),
{total_aws_spend},
0)

Then when you add this calculation to your field well, you can select sum and you should achieve the value you are expecting. Let me know if this helps!

5

Hello @DylanM , that won’t work as I want the max value for last 3 months and then sum the result.

1 Like
6

Hello @sarawgia, what level of granularity are you wanting the max value of? The max value for each day summed across 3 months? The max value across the entire 3 months? With a little more information, I should be able to assist you further.

1 Like
7

If you are wanting the daily max value summed across 3 months, it may look something like this:

sumOver(maxOver(ifelse(truncDate(‘MM’,{calendar_date})>=truncDate(‘MM’,addDateTime(-2,‘MM’,{Last Month})) AND truncDate(‘MM’,{calendar_date}) <= truncDate(‘MM’,{Last Month}){total_aws_spend}, NULL), [{calendar date}], PRE_AGG), [], PRE_AGG)

Nesting the maxOver insider of the sumOver should allow you to receive a daily max, ensure it is only within the 3 month range, then sum across those values. If you add this field into a field well on a visual, you will want to make sure the aggregation is either set to min or max. Otherwise it will sum duplications of the same value.

8

I need the the max value across the entire 3 months, I used the above formula you suggested,

ifelse(
truncDate(‘MM’,{calendar_date})>=truncDate(‘MM’,addDateTime(-2,‘MM’,{Last Month})) AND truncDate(‘MM’,{calendar_date}) <= truncDate(‘MM’,{Last Month}),
{total_aws_spend},
0)

But I need without month granularity for each customer what is the latest 3 month revenue, which is calculated by taking max of each month and then summing it

image
image1087×1028 45.4 KB

1 Like
9

Hello @sarawgia, just to confirm, when you say the max of each month, you mean the single highest instance of revenue in that month. Is that correct? If so, you can slightly edit the suggestion I made in my previous response and that should work.

First, create a date month field with truncDate:
Date Month = truncDate('MM', {calendar_date})

Now, use this as the partition for the maxOver field and the remaining calculation should be unchanged:
sumOver(maxOver(ifelse({Date Month} >=truncDate(‘MM’,addDateTime(-2,‘MM’,{Last Month})) AND {Date Month} <= truncDate(‘MM’,{Last Month}), {total_aws_spend}, NULL), [{Date Month}], PRE_AGG), [], PRE_AGG)

The maxOver value will first check if the date is within the past 3 months, then it will also return the max date for each month. Then, since it is wrapped in a sumOver, it will sum the max values from the last 3 months. This should help you find the value you are looking for.

Also, if you want to see the max values for each month rather than the 3 months combined, you can use the nested max over statement on its own.

maxOver(ifelse({Date Month} >=truncDate(‘MM’,addDateTime(-2,‘MM’,{Last Month})) AND {Date Month} <= truncDate(‘MM’,{Last Month}), {total_aws_spend}, NULL), [{Date Month}], PRE_AGG)

Using a mix of these calculations will be the best way to find the values that you mentioned above. Any time you want to alter the granularity level, you can change the partition field value. I will include some documentation below on these functions as well. Thank you!

10

Thanks @DylanM. I’ll try this out!

1 Like