Announcements
cancel
Showing results for
Did you mean:
Partner - Creator

## How to consider Max Month for Individual Countries in a table

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

Labels (2)

• ### set Analysis Measures

3 Replies

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

Partner - Creator
Author

@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.

Best Regards,

Vishal

@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:
[Prodduct Type],
date([Month Year], 'MMM YYYY') as [Month Year],
Share
FROM
[Test.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join(Data)
[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)