topic Re: Set the most recent date as a default in Chart unless other data is selected in QlikView

Set the most recent date as a default in Chart unless other data is selected

DataAnalytics — Fri, 26 Jun 2020 19:15:18 GMT

I am looking to create a pie chart that would show SUM of each category (attached Excel file) for the most current date (as a default). I can not use set analysis, because I also need an option to filter different dates. I am not having luck with it, when I create a pie chart, I get SUM of all values and I can not get the expression right to show only the most current date. I used max(date) function but without any results. Please let me know if you have any ideas 🙂

Re: Set the most recent date as a default in Chart unless other data is selected

Saravanan_Desingh — Sat, 27 Jun 2020 00:09:46 GMT

Are you looking something like this?

tab1: LOAD * INLINE [ Date, Category, Items 6/24/2020, A, 4 6/24/2020, B, 9 6/24/2020, C, 7 6/24/2020, D, 2 6/25/2020, A, 6 6/25/2020, B, 4 6/25/2020, C, 8 6/25/2020, D, 2 6/26/2020, A, 9 6/26/2020, B, 7 6/26/2020, C, 5 6/26/2020, D, 2 ]; Left Join(tab1) LOAD Max(Date) As Max_Date Resident tab1;

Re: Set the most recent date as a default in Chart unless other data is selected

Saravanan_Desingh — Sat, 27 Jun 2020 00:11:12 GMT

Dimension: Category

Expression with out Set Analysis:

Sum(if(Date=Max_Date,Items))

Re: Set the most recent date as a default in Chart unless other data is selected

DataAnalytics — Sat, 27 Jun 2020 12:26:20 GMT

Thank you for your reply, but this does not solve the problem. I need to be able to select another date as seen in the picture. This solutions shows only the most current date...When I delete the left join from the script and change the expression to Sum(Items), I get a whole summary. But I would like it to show the most current data and also have the option to select another week's data.

Re: Set the most recent date as a default in Chart unless other data is selected

Saravanan_Desingh — Sat, 27 Jun 2020 14:50:59 GMT

So, when there is no selection to Date, the Max Date has to be selected. Otherwise the Selected Date should be displayed.

tab1: LOAD * INLINE [ Date, Category, Items 6/24/2020, A, 4 6/24/2020, B, 9 6/24/2020, C, 7 6/24/2020, D, 2 6/25/2020, A, 6 6/25/2020, B, 4 6/25/2020, C, 8 6/25/2020, D, 2 6/26/2020, A, 9 6/26/2020, B, 7 6/26/2020, C, 5 6/26/2020, D, 2 ]; tab2: LOAD Max(Date) As Max_Date Resident tab1; Let vMaxDt=Peek('Max_Date'); Drop Table tab2;

Re: Set the most recent date as a default in Chart unless other data is selected

Saravanan_Desingh — Sat, 27 Jun 2020 14:52:23 GMT

Expression:

Sum(If(Date=If(GetFieldSelections(Date),GetFieldSelections(Date),vMaxDt),Items))

Re: Set the most recent date as a default in Chart unless other data is selected

DataAnalytics — Sat, 27 Jun 2020 17:43:28 GMT

This works EXACTLY how I wanted it. Thank you so much!!!

Re: Set the most recent date as a default in Chart unless other data is selected

Saravanan_Desingh — Sat, 27 Jun 2020 18:01:49 GMT

Please mark the solution if answered.