Count on percentage in expression

[#1]

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!),

Al

[Gysbert_Wassenaar]

=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%'))

[#3]

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!

[#4]

=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?

[#5]

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,

Al

[#6]

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()

)

[#7]

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

[#8]

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()









)

[#9]

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?

[#10]

maybe you could post a sample dashboard?