# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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
Contributor III

## Re: Count if combined with sum and total

Thanks

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

New Contributor III

## Re: Count if combined with sum and total

 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:

20152016
12 weeks +< 12 weeks
 12 weeks +
< 12 weeks
apple
banana
orange
pear

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!

New Contributor III

## Re: Count if combined with sum and total

Try somethng like that :

Quantity /  aggr(sum( Quantity) , Product)

New Contributor III

## Re: Count if combined with sum and total

Thanks both, gonna try these later and let you know