Pivot Table Calculate Percentage for column total only and ignore row dimension

[alanwong1178]

I would like to create a pivot table with row dimension : Sales Region , Sales city and column dimension: Sales Month.

For sales Shr per region field , I would like to get the share of sales of each city based on the total of each region for each month.

For example , China in Jan . I would like to get the shar of Hong Kong , Shanghai , Beijing based on total of China region in Jan

I created set analysis sum(sales)/sum(<sales region>sales). This gives a this wrong result because qliksense calculcated the shr based on the total sales of China region in Jan , Feb and March instead of just a month.

Grateful if share  your thought on how to write the set analysis to fulfill my needs

[Oleg_Troyansky]

Hi Alan,

Unfortunately Set Analysis cannot be used here, because it cannot be sensitive to your Dimensions (e.g. Month). Set Analysis conditions are evaluated "globally", outside of the boundaries of your chart.

Luckily, the solution for this problem is rather simple - you need to use the TOTAL qualifier for your sales formula - something like this:

sum(Sales)/sum(TOTAL <Region> Sales)

This formula will aggregate sales by Region and Month (in the numerator) and divide it by the total sales within the Region, still for the same Month.

Using this occasion, let me invite you to my session on Set Analysis and AGGR at the virtual Masters Summit for Qlik, that will take place online on March 1st. Secure your seat here. 

Cheers,

[alanwong1178]

sum(Sales)/sum(TOTAL <Region> Sales). This formula does not work because it  because qliksense calculcated the shr based on the total sales of each region in Jan , Feb and March instead of just a month.

My requirement is calculating the sales share of each city based on the total sales of each region for one month only. For example , sales share of Hong Kong in Jan. I would like to get the sales share of Hong Kong in Jan by 

Sales in Hong Kong in Jan /Sales in China Jan .

This formula sum(Sales)/sum(TOTAL <Region> Sales) results in Sales in Hong Kong in Jan /Sales in China Jan,Feb,Mar which is not what i want.

[Oleg_Troyansky]

This should work, then:

sum(Sales)/sum(TOTAL <Region, Month> Sales)