topic Re: Set Analysis Date Variable in QlikView

Set Analysis Date Variable

Karl_Hart — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Set Analysis Date Variable

Chanty4u — Fri, 09 Mar 2018 09:18:13 GMT

what is the variable values?

Re: Set Analysis Date Variable

Karl_Hart — Fri, 09 Mar 2018 09:28:19 GMT

The idea is that you can enter any date you like and can look at the most recent transaction before that date. In my example I've set vDateFrom to be 05/01/2017

Re: Set Analysis Date Variable

YoussefBelloum — Fri, 09 Mar 2018 09:47:55 GMT

Hi,

Please find attached the app with the correct formula

Re: Set Analysis Date Variable

Karl_Hart — Fri, 09 Mar 2018 09:59:48 GMT

Youssef,

Thank you very much, but that shows me the sum of all transactions lbefore the selected date, I need to only see the sum of the transactions on the last date before the selected date. This will be different for each Company / Product combination

Re: Set Analysis Date Variable

YoussefBelloum — Fri, 09 Mar 2018 10:06:20 GMT

Thats what I see on the table.. what is the wrong value ?

Re: Set Analysis Date Variable

chinnuchinni — Fri, 09 Mar 2018 10:08:57 GMT

You can use this expression for sales:

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

Re: Set Analysis Date Variable

Karl_Hart — Fri, 09 Mar 2018 10:09:57 GMT

For example,

Company ABC Ltd and Product 1. The last date was 03/01/2017. The sales on that day was 9000, your solution picks up that and the 5000 from 01/01/2017 and so shows 14000

Thanks

Re: Set Analysis Date Variable

MK_QSL — Fri, 09 Mar 2018 10:16:09 GMT

May be use something like below

Expression

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

FirstSortedValue({<IndexDate = {'<$(=Date(vDateFrom))'}>}Aggr(SUM({<IndexDate = {'<$(=Date(vDateFrom))'}>}Sales),IndexDate,Company,Product),-Aggr(IndexDate,IndexDate,Company,Product))

Re: Set Analysis Date Variable

YoussefBelloum — Fri, 09 Mar 2018 10:19:31 GMT

Sorry.. you can use praveen solution below.

Re: Set Analysis Date Variable

Karl_Hart — Fri, 09 Mar 2018 10:22:00 GMT

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

Thanks

Re: Set Analysis Date Variable

MK_QSL — Fri, 09 Mar 2018 10:29:23 GMT

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

Re: Set Analysis Date Variable

YoussefBelloum — Fri, 09 Mar 2018 10:45:12 GMT

Hi,

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

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

Re: Set Analysis Date Variable

MK_QSL — Fri, 09 Mar 2018 11:32:39 GMT

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

Re: Set Analysis Date Variable

YoussefBelloum — Fri, 09 Mar 2018 13:30:44 GMT

Thank you so much