topic Re: Expression as Set Analysis Modifier in QlikView

Expression as Set Analysis Modifier

Wed, 18 Jun 2014 16:57:27 GMT

Hi,

I am trying to produce a Count of All PO Numbers that have a spend of less than $5000. Each PO can have multiple Line Amounts and hence I need to ensure that sum of all Line Amounts for a PO total to less than $5000.

I tried the below formula but failed. I will appreciate any help.

Count({$<(aggr(Sum([Line Amount]),[PO Number])={"<5000"})>}[PO Number])

Re: Expression as Set Analysis Modifier

Anonymous — Wed, 18 Jun 2014 17:10:19 GMT

Hello.

Please, try this expression:

Count(Distinct Aggr(If(Sum([Line Amount]) < 5000, [PO Number]), [PO Number]))

Regards.

Re: Expression as Set Analysis Modifier

Wed, 18 Jun 2014 17:11:30 GMT

hi try this:

Count({< [PO Number] = {"=sum([Line Amount])>5000"} >} [PO Number])

Best Regards.

Re: Expression as Set Analysis Modifier

Wed, 18 Jun 2014 18:24:48 GMT

I tried but this did not seem to work.

Re: Expression as Set Analysis Modifier

Wed, 18 Jun 2014 18:26:34 GMT

This did work, however it produces incorrect values when user selections are made because it is not enabled for user selected data. Could you guide how can I ensure user selections are considered?

Thank you!

Re: Expression as Set Analysis Modifier

Anonymous — Wed, 18 Jun 2014 19:25:24 GMT

Hi.

For what I can see, both my solution and Oswaldo's give the same results (besides the Distinct in the Count). Can you provide a sample qvw so we can better help you?

Re: Re: Expression as Set Analysis Modifier

Wed, 18 Jun 2014 21:33:05 GMT

Please see attached a sample qvw file I created.

Re: Re: Re: Expression as Set Analysis Modifier

Anonymous — Wed, 18 Jun 2014 22:30:35 GMT

Deepinder,

My expression,

Count(Distinct Aggr(If(Sum([Line Amount]) < 5000, [PO Number]), [PO Number]))

is returning what I think is the correct value. Oswaldo's will also work with 2 simple corrections:

Count(Distinct {<[PO Number] = {"=Sum([Line Amount]) < 5000"}>} [PO Number])

Both will return the count of distinct PO Numbers that amount to less than $5000.

See the attached qvw. I have selected the PO Numbers with less than $5000. Notice the matching count of 36. If you clear the selection will see that our expressions still count 36.

Regards.

Re: Re: Re: Expression as Set Analysis Modifier

Thu, 19 Jun 2014 19:43:22 GMT

This worked great! Thank you guys...

Just a quick follow-up question. How can I apply the same concept if I were to try to find Count of PO's with spend between the range $5000 and $10000.

Thank you!

Re: Re: Re: Expression as Set Analysis Modifier

Anonymous — Thu, 19 Jun 2014 19:51:12 GMT

Hello, Deepinger.

It would be like this:

Count(Distinct Aggr(If(Sum([Line Amount]) > 5000 and Sum([Line Amount]) < 10000, [PO Number]), [PO Number]))

Count(Distinct {<[PO Number] = {"=Sum([Line Amount]) > 5000 and Sum([Line Amount]) < 10000"}>} [PO Number])

Best Regards.

Re: Re: Re: Expression as Set Analysis Modifier

Thu, 19 Jun 2014 19:56:50 GMT

Thank you! You guys are the bets... Appreciate all the guidance!

Regards,

Deepinder