topic Re: PIVOT TABLE in QlikView

PIVOT TABLE

marikabi — Wed, 25 Nov 2020 16:16:04 GMT

Hello Everyone,

I have a problem using a pivot table.

I have these columns:

WEEK
CITY
COUNTRY
SALES

Well, what I'd like to do is to calculate the percentage of the sales in every city, per week.

I need to find a way to calculate the ratio between SUM(SALES) and the subtotal (per week and city).

Could you please help me?

Many thanks in advance

Re: PIVOT TABLE

sunny_talwar — Fri, 18 Nov 2016 11:46:08 GMT

Would you be able to provide a sample where we can help you? It would be also be helpful to know the expected output based on your provided sample

Re: PIVOT TABLE

Anonymous — Fri, 18 Nov 2016 11:51:22 GMT

If you have all of the above columns in your pivot and your sales exp is like sum(Sales)

try something like this for the ratio:

sum(Sales) / aggr(sum(total Sales),Country,City,Week)

sum(Sales) / sum(aggr(sum(total Sales),Country,City,Week))

Re: PIVOT TABLE

sunny_talwar — Fri, 18 Nov 2016 11:53:49 GMT

I would rather use TOTAL without Aggr() here

Sum(Sales)/Sum(TOTAL <Country, City, Week> Sales)

Re: PIVOT TABLE

marikabi — Fri, 18 Nov 2016 12:06:53 GMT

Thank you Sunny and Qlikdeez for your quick answers

Indeed, Sunny, you have solved my problem (just one thing, after TOTAL I put only WEEK)... maybe I wrote the message in an unclear way..but you are both really really kind, and you helped me a lot!

Thank you