show latest months data in totals in qliksense pivot table

[bharathkamath28]

Hi,

I want to show months data within a year in totals in qliksense pivot table.

Ex; Input table

Expected output:

I tried using a variable vMaxMonth= aggr(max(Month Year),Year)

if( dimensionality()=2,

sum(value),

if( dimensionality()=1,

sum({<month year ={"=$(vMaxMonth)"}>} value)))

This is not working for me.

Please suggest the best solution.

Thanks,

[RsQK]

Hi, check the attached .qvf

[SunilChauhan]

you Can Check this ,

Sample i have created for your cas

Test:
Load *,Date(MonthName(Date),'MMM-YY') as Month,num(MonthName(Date)) as MonthNum,Year(Date) as Year inline [
Date,City1,City2,City3
1/9/2022,100,200,300
1/8/2022,200,100,400
1/7/2022,300,200,100
1/9/2021,100,200,300
1/8/2021,200,100,400
1/7/2021,300,200,100
];
NoConcatenate
Load Year,
Max(MonthNum) as MaxMonthNum
resident Test
Group By Year;

Formula used

If(Dimensionality()=1,Sum(IF( MonthNum=MaxMonthNum,City1)),Sum(City1))

If(Dimensionality()=1,Sum(IF( MonthNum=MaxMonthNum,City2)),Sum(City2))

If(Dimensionality()=1,Sum(IF( MonthNum=MaxMonthNum,City3)),Sum(City3))

[bharathkamath28]

Thanks @RsQK @SunilChauhan , I will check and verify.