Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

noviceqlik
New Contributor III

Count if combined with sum and total

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

Tags (3)
6 Replies
Highlighted
isingh30
Contributor III

Re: Count if combined with sum and total

Can you share your data?

Thanks

noviceqlik
New Contributor III

Re: Count if combined with sum and total

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

noviceqlik
New Contributor III

Re: Count if combined with sum and total

ProductDurationQuantityYear
apple< 12 weeks417.002015
banana12 weeks +948.002015
orange< 12 weeks987.002015
pear12 weeks +831.002015
apple12 weeks +582.002016
banana12 weeks +449.002016
orange< 12 weeks924.002016
pear< 12 weeks689.002016
apple12 weeks +513.002017

so in my pivot table I have this:

   

Header 1Header 2Header 3Header 4Header 5
20152016
12 weeks +< 12 weeks
12 weeks +
< 12 weeks
apple
banana
orange
pear


 

isingh30
Contributor III

Re: Count if combined with sum and total

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!

nicolas_sinquin
New Contributor III

Re: Count if combined with sum and total

Try somethng like that :

Quantity /  aggr(sum( Quantity) , Product)

noviceqlik
New Contributor III

Re: Count if combined with sum and total

Thanks both, gonna try these later and let you know

Community Browser