Count if combined with sum and total

[Anonymous]

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

[isingh30]

Can you share your data?

Thanks

[Anonymous]

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

[Anonymous]

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 +	< 12 weeks
apple
banana
orange
pear

[isingh30]

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!

[Anonymous]

Try somethng like that :

Quantity /  aggr(sum( Quantity) , Product)

[Anonymous]

Thanks both, gonna try these later and let you know