Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to use Set Analysis to sum until the last available record with data instead of the last calendar date

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:

CompaniesMonthYearAmount
Company120170110000
Company120161220000
Company2201701-
Company2201612-
Company220161135000
Company220161017000
Company3201701-
Company320161250000
Company320161110000
Company320161024000
Company320160940000
Company3201608-
Company3201607-
Company320160632000
Company320160518000

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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!!!

View solution in original post

6 Replies
sunny_talwar

Try this I guess

Sum(Aggr(If(MonthYear >= AddMonths(Max(TOTAL <Company> MonthYear), -12) and MonthYear <= Max(TOTAL <Company> MonthYear), Amount), MonthYear, Company))

Anonymous
Not applicable
Author

Nice try, but it doesn't really work.

Any other ideas?

sunny_talwar

Would you be able to share a sample where this isn't working?

Anonymous
Not applicable
Author

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.

sunny_talwar

I am not sure what the issue is, I seems to get the same number for Amount you are looking to get?

Capture.PNG

Anonymous
Not applicable
Author

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!!!