How to show the latest year data in the table using set expression

[LovelyKrishna]

Hi All,

i would like to show the data for the latest year in the report, i am getting the zero values for few records and i couldn't uncheck include zero values using data handling section because there are few request will have zero values in the data.

See the sample data set i have created for demo purpose.

Load * Inline [
Year,Brand,Sales,Country,Customer,Month
2019,Parle,450,India,A,Jan
2019,Parle,350,USA,B,Feb
2019,Good Day,400,UK,C,Mar
2019,Parle,300,China,D,Apr
2019,Parle,330,Japan,E,May
2019,Good Day,260,Russia,F,Jun
2018,Parle,4550,India,A,Jan
2018,Parle,3560,USA,B,Feb
2018,Good Day,4800,UK,C,Mar
2018,Parle,3000,China,D,Apr
2018,Parle,3300,Japan,E,May
2018,Good Day,2060,Russia,F,Jun
2018,Parle,1200,China,R,Apr
2017,Geekey,3070,China,L,Apr
2016,Geekey,4800,China,S,Apr
2016,Ramkey,6700,China,Z,Apr
2016,Ramkey,7700,China,Y,Apr

];

attached the screenshot for reference.

As per the attached screenshot, for the Sales measure i have used the below set expression.

if(Country='India' or Country='USA',1,Sum({<Year={'$(=Max(Year))'}>}Sales))

As per the above set expression which shows the values for only max year data but also shows the values as zero for other years in the report.

Here my requirement is i wanted to show only the max year values and others couldn't be shown like i want the report as table2 result in the attached screenshot for reference.

If you Can help me how to fix this issue that would be great indeed, Thanks in Advance.

[GaryGiles]

What is the purpose of your If logic?  If you are trying to show sales for the latest year, excluding Countries India and USA, try this:

sum({$<Country-={'India','USA'},Year={$(=Max(Year))}>} Sales)

I didn't put $(=Max(Year)) in quotes, because it should be a numeric field.

[MikeA]

Like GaryGiles I'm also a bit puzzled by the intended logic.

Table2 in your screenshot is a sum of all countries, if you exclude USA and India the total would be 1290 (660 Good Day + 630 Parle).

If you want to include USA and India to get the 2090 total as per your screenshot then all you need is -

Sum({<Year={'$(=Max(Year))'}>}Sales)

If you want to exclude USA and India to get the 1290 total then use this -

Sum({<Country-={'India','USA'},Year={'$(=Max(Year))'}>}Sales)

If you really want the if statement and but only show 2019 then use this -

if(only({<Year={'$(=Max(Year))'}>}Country)='India' or only({<Year={'$(=Max(Year))'}>}Country)='USA',1,Sum({<Year={'$(=Max(Year))'}>}Sales))

[LovelyKrishna]

Hi GaryGiles,

Thanks for your response.

So here the if logic will be used for the other data set, That means i do have another set of data and i will be using the separate calculation for the country INDIA and USA, Other than Country INDIA and USA i will be using the else part calculation.

I am not sure if i use the below expression, I will check and confirm.

sum({$<Country-={'India','USA'},Year={$(=Max(Year))}>} Sales)

Thank You so much.

[LovelyKrishna · Accepted solution]

Hi Mike,

It is working and Thank you so much and it was very helpful.

Regards, Krishna.