topic Sum to count results within a text box in App Development

Sum to count results within a text box

MrBosch — Wed, 30 Nov 2022 12:46:32 GMT

I am trying to add two count results in combination of text but I can't get it to work.

Simply stated: three lines, first two apple counts have special attributes and the third line will just need to ADD/SUM the first two counts.

=
'Red Sweet Apples: ' &Count({$<taste={'sweet'},color={'red'}>}apples) &CHR(10) &
'Green Sour Apples: ' &Count({$<taste={'sour'},color={'green'}>}apples) & CHR(10) &
'Total Apples: ' &Count({$<taste={'sweet'},color={'red'}>}apples) + Count({$<taste={'sour'},color={'green'}>}apples)

Re: Sum to count results within a text box

Or — Wed, 30 Nov 2022 12:59:03 GMT

Perhaps try using RangeSum() on the two counts, rather than straight addition? Or just write it as a single count with a union of sets?

Re: Sum to count results within a text box

MrBosch — Wed, 30 Nov 2022 13:07:18 GMT

I would love to try that Or, but I have no clue how to use your solution... Sorry.

I will try to find it searching for these key words though. Thanks.

Re: Sum to count results within a text box

Or — Wed, 30 Nov 2022 13:15:06 GMT

RangeSum(Count({$<taste={'sweet'},color={'red'}>}apples) ,Count({$<taste={'sour'},color={'green'}>}apples) )

I believe the set union syntax would be:

Count({$<taste={'sweet'},color={'red'}> + <taste={'sour'},color={'green'}> }apples)

Re: Sum to count results within a text box

MrBosch — Wed, 30 Nov 2022 13:16:52 GMT

Got it: union of sets

For those who are interested:

=count({<EXP1> + <EXP2>} apples)

So:

=
'Red Sweet Apples: ' &Count({$<taste={'sweet'},color={'red'}>}apples) &CHR(10) &
'Green Sour Apples: ' &Count({$<taste={'sour'},color={'green'}>}apples) & CHR(10) &
'Total Apples: ' &Count({$<taste={'sweet'},color={'red'} + {'sour'},color={'green'}>}apples)

Thanks Or for pointing me in the right direction!

Never thought it would be so difficult to just sum two numbers though... since it will not solve any question like:

= 'Total fruit: ' & count (apples) + count (pears)

Re: Sum to count results within a text box

Or — Wed, 30 Nov 2022 13:20:09 GMT

It would be more robust if you used RangeSum() rather than straight addition, if you do need to handle apples and pears. I believe you could also simply use parenthesis to tell Qlik how to parse this:

'1 + 1 = ' & (1+1)

Re: Sum to count results within a text box

MrBosch — Wed, 30 Nov 2022 13:47:36 GMT

Hmm... That puts me back where I started:

='1 + 1 = ' & (1+1)

Is exactly what I am trying to achieve: the easy way.

So now let's assume: ='1 + 1 = ' & (A+B)

I substitute

A= Count({$<taste={'sweet'},color={'red'}>}apples)
B= Count({$<taste={'sour '},color={'green'}>}apples)

and Qlikview seems not to be able to handle it. :-S

Re: Sum to count results within a text box

MrBosch — Wed, 30 Nov 2022 14:00:40 GMT

Okay, sorry. Must have been a typo or something... (I couldn't imagine QV would not be able to handle it).

So if you combine text and want to add (or sum) two calculations:

= 'Result of two expressions: ' &(count A + count B) & 'some more text' &CHR(10)

so: (count A = count B) is:

&(Count({$<weight={">80"},type={'apples'}>}fruit) + Count({$<weight={">94"},type={'pears'}>}fruit))

Thanks Or for helping me getting there!