topic Re: Count on percentage in expression in QlikView

Count on percentage in expression

Wed, 24 Sep 2014 12:47:09 GMT

Hi All,

I've an expression in a straight table that calculates the percentage difference in product usage between dates (below):

=NUM(IF((count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate)=0 AND count({<DLDate = {'=DLDate>TLDate'}>}DLDate)>0),1,

(count({<DLDate = {'=DLDate>TLDate'}>}DLDate)-

count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate))/count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate)),'##0%')

This works fine, but I'd like to build a similar expression that counts the number of the above that are greater than 49% and can't work out how to get it going. I'm having trouble getting the count to react to the percentage so any help would be hugely appreciated.

Thanks in advance (and I hope I've explained this correctly!),

Re: Count on percentage in expression

Gysbert_Wassenaar — Wed, 24 Sep 2014 13:14:25 GMT

=IF(IF((count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate)=0 AND count({<DLDate = {'=DLDate>TLDate'}>}DLDate)>0),1,

(count({<DLDate = {'=DLDate>TLDate'}>}DLDate)-

count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate))/count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate))>0.49,

NUM(IF((count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate)=0 AND count({<DLDate = {'=DLDate>TLDate'}>}DLDate)>0),1,

(count({<DLDate = {'=DLDate>TLDate'}>}DLDate)-

count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate))/count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate)),'##0%'))

Re: Count on percentage in expression

Wed, 24 Sep 2014 15:12:32 GMT

That gives me a percentage answer (which in a way is helpful) but I'm looking for a count of the number of results that come back at greater than 49%, rather than a percentage. Thanks in advance!

Re: Count on percentage in expression

Wed, 24 Sep 2014 15:15:42 GMT

=IF(IF((count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate)=0 AND count({<DLDate = {'=DLDate>TLDate'}>}DLDate)>0),1,

(count({<DLDate = {'=DLDate>TLDate'}>}DLDate)-

count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate))/count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate))>0.49,

NUM(IF((count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate)=0 AND count({<DLDate = {'=DLDate>TLDate'}>}DLDate)>0),1,

(count({<DLDate = {'=DLDate>TLDate'}>}DLDate)-

count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate)),

NUM(IF((count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate)=0 AND count({<DLDate = {'=DLDate>TLDate'}>}DLDate)>0),1,

(count({<DLDate = {'=DLDate>TLDate'}>}DLDate)-

count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate))/count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate)),'##0%'))

if, greater than 49%, count, otherwise just show the percentage?

Re: Count on percentage in expression

Wed, 24 Sep 2014 15:22:21 GMT

Hi Katarzyna,

That's not quite correct, but nearly there - the best way to put it would be:

If greater than 49%, count, otherwise do not count.

Cheers,

Re: Count on percentage in expression

Wed, 24 Sep 2014 16:41:17 GMT

Try this Alex:

=IF(IF((count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate)=0 AND count({<DLDate = {'=DLDate>TLDate'}>}DLDate)>0),1,

(count({<DLDate = {'=DLDate>TLDate'}>}DLDate)-

count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate))/count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate))>0.49,

NUM(IF((count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate)=0 AND count({<DLDate = {'=DLDate>TLDate'}>}DLDate)>0),1,

(count({<DLDate = {'=DLDate>TLDate'}>}DLDate)-

count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate)),

null()

)

Re: Count on percentage in expression

Thu, 25 Sep 2014 12:36:15 GMT

There's an error in the expression somewhere there - we're nearly there though!

Re: Count on percentage in expression

Thu, 25 Sep 2014 12:41:37 GMT

try this:

IF(IF((count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate)=0 AND count({<DLDate = {'=DLDate>TLDate'}>}DLDate)>0),1, (count({<DLDate = {'=DLDate>TLDate'}>}DLDate)-count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate))/count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate))>0.49,

IF((count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate)=0 AND count({<DLDate = {'=DLDate>TLDate'}>}DLDate)>0),1,(count({<DLDate = {'=DLDate>TLDate'}>}DLDate)-count({<DLDate = {'=DLDate<TLDate and DLDate>TLDate-30'}>}DLDate))),

null()

)

Re: Count on percentage in expression

Thu, 25 Sep 2014 13:40:14 GMT

That's brilliant and seems to work great but I'm getting higher results than I was expecting. In Excel I have a formula that calculates the percentage and another that references it thus: =IF([percentage cell]>49%,"1","0"). I simply sum this column to give me the total number of instances over 49%.

It was my understanding that if I counted the instances over 49% in my Qlik expression this would give the same result but it's not working out that way - is this the correct approach?

Re: Count on percentage in expression

Fri, 26 Sep 2014 11:32:13 GMT

maybe you could post a sample dashboard?