topic Re: set analysis formula in QlikView

set analysis formula

fabricecallegari — Mon, 16 Feb 2015 20:54:15 GMT

Hi,

I need your help to create a fromula.

I have this data :

I want to know how many distinct tickets available by attribut with the special condition : if the sum of qty by ticket must be different than 0, else the ticket is not taken.

So the results must be :

A1 : 1 (ticket T2)

A2 : 2 (ticket T1 and T2)

In fact, I want the sum of the third column by attributs :

and when I remove "Tick" the results are false;

Any ideas ?

Re: set analysis formula

robert_mika — Mon, 16 Feb 2015 22:30:51 GMT

So in your last example the last two 1 (under if(sum(Qty)...will be evaluated to 0?

Re: set analysis formula

jagan — Tue, 17 Feb 2015 06:42:17 GMT

Hi,

Try this expression using Aggr()

Sum(Aggr(If(Sum(Qty) = 0, 0, 1), Tick))

Hope this helps you.

Regards,

Jagan.

Re: set analysis formula

fabricecallegari — Tue, 17 Feb 2015 08:47:35 GMT

no, I would like to have :

A1 : 1

A2 : 2

Re: set analysis formula

fabricecallegari — Tue, 17 Feb 2015 08:49:03 GMT

thank you Jagan but it doesn't work :

Re: set analysis formula

jagan — Tue, 17 Feb 2015 09:26:13 GMT

Hi,

Please find attached file for solution.

I used this expression, it is working.

=Sum(Aggr(If(Sum(qty) = 0, 0, 1), tick, attribute))

Regards,

Jagan.

Re: set analysis formula

maxgro — Tue, 17 Feb 2015 10:00:27 GMT

=count(distinct Aggr(If(Sum(q)=0, null(), t), t, a))

Re: set analysis formula

fabricecallegari — Tue, 17 Feb 2015 10:00:34 GMT

thank a lot !

Re: set analysis formula

Tue, 17 Feb 2015 10:03:34 GMT

Hi Fabrice.

please see attached, another option for you which doesn't require nesting an if statement.

Hope that helps

Joe