Hello. There's a slightly convoluted problem statement that I have which I'm trying to solve. Below is a table with sample data:
| date | category | merchant | type | transaction | ticket |
| 01-01-2020 | C1 | M1 | T1 | 100 | 5 |
| 01-01-2020 | C1 | M1 | T2 | 100 | 10 |
| 01-01-2020 | C1 | M2 | T3 | 300 | 15 |
| 01-01-2020 | C2 | M1 | T4 | 400 | 20 |
| 01-01-2020 | C2 | M4 | T5 | 500 | 25 |
In this, I need to use aggr functions where I need to aggregate at different levels depending on the category column. For example - If category = 'C1' then my aggr will look like:
Sum(Aggr(Max({<category = {'C1'}>} transaction), date, merchant))
For category = 'C2', it will look like:
Sum(Aggr(Max({<category = {'C2'}>} transaction), date, type))
The above function works if I apply this in a if condition, like this:
=if(category = 'C1', Sum(Aggr(Max({<category = {'C1'}>} transaction), date, merchant)),
if(category = 'C2', Sum(Aggr(Max({<category = {'C1'}>} transaction), date, type))
)
)
The problem is that I need to apply this for specific dates. I can't use these in dimensions because the logic is a bit convoluted. Eg: I am looking to get the sum(max(transactions) for yesterday and divide it by the average(sum(max(transactions))) for the last 2 weeks. So I tried something like this, which isn't giving me the output that I'm looking for.
=if(category = 'C1' and date = today()-1, Sum(Aggr(Max({<date = {'(=Date(Today() - 1))'}, category = {'C1'}>} transaction), date, merchant)),
if(payment_category = 'C2' and date = today()-1, Sum(Aggr(Max({<date = {'(=Date(Today() - 1))'}, category = {'C2'}> } transaction), date, type))
)
)
The above expression is is only for yesterday's numbers. I'm assuming that when I'm using the right expression, I'd be able to do <= date(today() - 2) and >= date(today() - 14) to get the last 2 week's values as well.
Any help is appreciated. Been struggling with this for a while now. Thanks.
.png "Former Employee"){kind=icon}
