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

[DataAnalytics]

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 🙂

[Saravanan_Desingh]

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;

[Saravanan_Desingh]

Dimension: Category

Expression with out Set Analysis:

Sum(if(Date=Max_Date,Items))

 

[DataAnalytics]

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.

[Saravanan_Desingh · Accepted solution]

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;

[Saravanan_Desingh]

Expression:

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

[DataAnalytics]

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

[Saravanan_Desingh · Accepted solution]

Please mark the solution if answered.