How to consider Max Month for Individual Countries in a table

[vishalmanu]

HI All,

I have a requirement  where i need to calculate the share values for each country only by showing the max months value by product wise in  a pivot table in qliksense.

Attached is the sample data, where the max month year for each country is different. And i wanted to show the Country wise Shares (Individual Max months based on country) under each product type in a pivot table.

I tried to use the Aggr in the SET analysis to calculate the Max(Month) grouped y Country, but could not succeed.

Kindly help m with this scenario.

Best Regards,

Vishal

[Kushal_Chawda]

@vishalmanu  can you tell me what is the expected output for anyone country from sample data ?

[vishalmanu]

@Kushal_Chawda , I want the output as below in the Pivot table in Qliksense.

Eg. for the country Argentina and Australia the max months for all three products are June 2020 and Jul 2020 respectively.. So the output in the pivot table for these countreis should be as below

Country	Product 1 Max Month	Product 1 Max Month - (Max Month-1)	Product 12 Max Month	Product 12 Max Month - (Max month-1)	Product 12 Max Month	Product 12 Max Month - (Max month-1)
Argentina	Value of June 2020 sinceJune 2020 is max month for Product 1 in Argentina	Value for May 2020 (Max Month -1)	Value of June 2020 sinceJune 2020 is max month for Product 1 in Argentina	Value for May 2020 (Max Month -1)	Value of June 2020 sinceJune 2020 is max month for Product 1 in Argentina	Value for May 2020 (Max Month -1)
Australia	Value of July 2020 sinceJuly 2020 is max month for Product 1 in Australia	Value for June 2020 (Max Month -1)	Value of July 2020 sinceJuly 2020 is max month for Product 1 in Australia	Value for June 2020 (Max Month -1)	Value of July 2020 sinceJuly 2020 is max month for Product 1 in Australia	Value for June 2020 (Max Month -1)

Kindly let me know if the above example is not clear.

Thanks in advance.

Best Regards,

Vishal

[Kushal_Chawda]

@vishalmanu  Set analysis won't work here as you need to calculate max month for combination of country and product. Set analysis works once per chart and not by individual dimension values. So you can try below expression. Assuming sum(Share)  is your expression

= sum(aggr(if([Month Year]=max(TOTAL<Country,[Prodduct Type]>[Month Year]) ,Share),Country,[Prodduct Type],[Month Year]))

Below is the script approach,

Data:
LOAD Country, 
     [Prodduct Type], 
     date([Month Year], 'MMM YYYY') as [Month Year],
     Share
FROM
[Test.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join(Data)
LOAD Country, 
     [Prodduct Type],
     date(max([Month Year]), 'MMM YYYY') as [Month Year],
     1 as MaxMonthFlag
Resident Data
Group by Country, 
     [Prodduct Type];

Now you can simply use below expression

=sum({<MaxMonthFlag={1}>}Share)