Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vishalmanu
Partner - Creator
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)
3 Replies
Kushal_Chawda

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

vishalmanu
Partner - Creator
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

 

CountryProduct 1 Max Month Product 1 Max Month - (Max Month-1)Product 12 Max MonthProduct 12 Max Month - (Max month-1)Product 12 Max MonthProduct 12 Max Month - (Max month-1)
ArgentinaValue of June 2020 sinceJune 2020 is max month for Product 1 in ArgentinaValue for May 2020 (Max Month -1)Value of June 2020 sinceJune 2020 is max month for Product 1 in ArgentinaValue for May 2020 (Max Month -1)Value of June 2020 sinceJune 2020 is max month for Product 1 in ArgentinaValue for May 2020 (Max Month -1)
AustraliaValue of July 2020 sinceJuly 2020 is max month for Product 1 in AustraliaValue for June 2020 (Max Month -1)Value of July 2020 sinceJuly 2020 is max month for Product 1 in AustraliaValue for June 2020 (Max Month -1)Value of July 2020 sinceJuly 2020 is max month for Product 1 in AustraliaValue 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)