Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Karl_Hart
Creator
Creator

Set Analysis Date Variable

All,

I am using a varible to select a date, a point in time. I have created a chart that calculates the LAST transaction BEFORE that date for each Company / Product Combination. This is working as it should (in the example it is showing the last date being the 2nd or the 3rd

          =Max({<IndexDate = {'<$(=Date(vDateFrom))'}>}IndexDate)

I then want to look at the sum of the sales that happened on that date, and this is where I'm having the issue

          =Sum({<IndexDate={'$(=Date(Max({<IndexDate = {'<$(=Date(vDateFrom))'}>}IndexDate)))'}>}Sales)

It is only doing the calculation at a global, not at the company / product level as above (i.e. only doing the calculation for the sales on the 3rd

Please see attached for details. I dont know if I need to reference the previous column (I understand this cant be done) or do some sort of aggr function, but nothing I've tried works and I dont understand why this one doesn't

I want it to show

Company               Product               Last Trans Date               Sales on that Date

ABC Ltd                 Product 1            03/01/2017                        9000

ABC Ltd                 Product 2            02/01/2017                        1000

XYZ Ltd                 Product 1            02/01/2017                        7000

XYZ Ltd                 Product 2            03/01/2017                        13000

Thanks,

Karl

14 Replies
Karl_Hart
Creator
Creator
Author

Perfect, thank you very much. I initially though of using firstsortedvalue but can never get the expression right

Thanks

MK_QSL
MVP
MVP

Great that my solution served the purpose.

If you want to learn more about Set Analysis you can check my Udemy course on Set Analysis.

Become an Expert of Qlik Set Analysis

YoussefBelloum
Champion
Champion

Hi,

can you please share your opinion to this: (praveen's working solution) compared to yours ? pro and cons

max({<IndexDate = {'<$(=Date(vDateFrom))'} >}Sales)



MK_QSL
MVP
MVP

Say we have one more line in our data.

Means for same Company,Product and Date, we have two lines of sales.

02/01/2017, XYZ Ltd, Product 1 ,8000

In that case, Praveen's solution will give 8000, which is Max.

My solution will give 15,000 (i.e. 7000+8000).

Now, it's depends on what end user wants... Total Sales or Max Sales.

Hope this will make clear idea what I mean to say..!!

YoussefBelloum
Champion
Champion

Thank you so much