Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
My expression looks currently like this:
Sum({$<[Calendar Date] = {"<=$(=Date(MonthEnd(Date(Today()))))>=$(=Date(AddMonths(MonthStart(Date(Today())), - 12)))"}>} [Amount])
This is how the data looks like:
Companies | MonthYear | Amount |
---|---|---|
Company1 | 201701 | 10000 |
Company1 | 201612 | 20000 |
Company2 | 201701 | - |
Company2 | 201612 | - |
Company2 | 201611 | 35000 |
Company2 | 201610 | 17000 |
Company3 | 201701 | - |
Company3 | 201612 | 50000 |
Company3 | 201611 | 10000 |
Company3 | 201610 | 24000 |
Company3 | 201609 | 40000 |
Company3 | 201608 | - |
Company3 | 201607 | - |
Company3 | 201606 | 32000 |
Company3 | 201605 | 18000 |
This is what I want to achieve:
Example: *Company1 has data until 201701 --> sum data from 201601 until 201701
*Company2 has data until 201611 --> sum data from 201511 until 201611
*Company3 has data until 201612 --> sum data from 201512 until 201612
But because I am using Today() as my limit I am having the following displayed: (THIS IS NOT WHAT I WANT TO ACHIEVE)
Example: *Company1 has data until 201701 --> sum data from 201601 until 201701
*Company2 has data until 201611 --> sum data from 201601 until 201701
*Company3 has data until 201612 --> sum data from 201601 until 201701
How do I need to change the expression so that my dashboard will sum the [Amount] field from the last available month with data for every company until the 12 months before that date?
PS: Today() = 23 Jan 2016
I figured out what expression I have to use:
I just had to change Today() with the maximum month where amount was available
Sum({$< [Calendar Date] = {"<=$(=Date(MonthEnd(Date(Date(MonthEnd(Date(Max(Aggr(Max({$<[Calendar Date] = {"<=$(=Date(MonthEnd(Date(Today()))))"}, [Amount] = {"<>0"}>} [Calendar Date]), [Companies])))))))))>=$(=Date(AddMonths(MonthStart(Date(Date(MonthEnd(Date(Max(Aggr(Max({$<[Calendar Date] = {"<=$(=Date(MonthEnd(Date(Today()))))"}, [Amount] = {"<>0"}>} [Calendar Date]), [Companies]))))))), -12)))"}>} [Amount])
Thank you stalwar1 for your help!!!
Try this I guess
Sum(Aggr(If(MonthYear >= AddMonths(Max(TOTAL <Company> MonthYear), -12) and MonthYear <= Max(TOTAL <Company> MonthYear), Amount), MonthYear, Company))
Nice try, but it doesn't really work.
Any other ideas?
Would you be able to share a sample where this isn't working?
I have attached an application with your expression, by looking inside of the application you will be able to understand where my difficulty lies with creating the desired chart.
I am not sure what the issue is, I seems to get the same number for Amount you are looking to get?
I figured out what expression I have to use:
I just had to change Today() with the maximum month where amount was available
Sum({$< [Calendar Date] = {"<=$(=Date(MonthEnd(Date(Date(MonthEnd(Date(Max(Aggr(Max({$<[Calendar Date] = {"<=$(=Date(MonthEnd(Date(Today()))))"}, [Amount] = {"<>0"}>} [Calendar Date]), [Companies])))))))))>=$(=Date(AddMonths(MonthStart(Date(Date(MonthEnd(Date(Max(Aggr(Max({$<[Calendar Date] = {"<=$(=Date(MonthEnd(Date(Today()))))"}, [Amount] = {"<>0"}>} [Calendar Date]), [Companies]))))))), -12)))"}>} [Amount])
Thank you stalwar1 for your help!!!