Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all
I am constructing a pivot table in Qlik Sense. I am looking for an expression that counts a piece of string (in my case: "< 12 weeks" or "12 weeks +") and then find the overall percentage for that product.
In my pivot table I have 'product' in the rows. then I will add two measures: (1) to find the percentage of those that are "< 12 weeks" for that product (2) to find the percentage of those that are "12 weeks +" for that product.
So the expression should say, for example, count if "12 weeks +" divide by the total number for product
Product Duration Quantity
apple < 12 weeks 123
banana 12 weeks + 22
orange 12 week + 99
pear < 12 weeks 12
apple 12 week 65
banana < 12 weeks 28
Can you share your data?
Thanks
I can't share the data unfortunately, I have used dummy data in my first post. I have added a new field (Quantity) that I forgot and should have added
So in my dummy data, "apple" can only either be "< 12 weeks" or "12 weeks +" and likewise "banana" and so on. So in my pivot table I need to find the percentage for apple "< 12 weeks" over the total quantity of all of apple in my table i.e. the sum of quantity for apple
Product | Duration | Quantity | Year |
apple | < 12 weeks | 417.00 | 2015 |
banana | 12 weeks + | 948.00 | 2015 |
orange | < 12 weeks | 987.00 | 2015 |
pear | 12 weeks + | 831.00 | 2015 |
apple | 12 weeks + | 582.00 | 2016 |
banana | 12 weeks + | 449.00 | 2016 |
orange | < 12 weeks | 924.00 | 2016 |
pear | < 12 weeks | 689.00 | 2016 |
apple | 12 weeks + | 513.00 | 2017 |
so in my pivot table I have this:
Header 1 | Header 2 | Header 3 | Header 4 | Header 5 | |
---|---|---|---|---|---|
2015 | 2016 | ||||
12 weeks + | < 12 weeks |
| < 12 weeks | ||
apple | |||||
banana | |||||
orange | |||||
pear | |||||
Try this is expression -
sum(if(Duration < '12 weeks', Quantity))
To get the percentage divide the result by 100 / check the percentage option under number tab.
Thank you!
Try somethng like that :
Quantity / aggr(sum( Quantity) , Product)
Thanks both, gonna try these later and let you know