topic Re: Set analysis for Customer having total greater than a value in QlikView

Set analysis for Customer having total greater than a value

Learner — Wed, 25 Nov 2020 16:16:04 GMT

Hello ,

I am trying to write a formula in text object where i wanted the selection i.e. set analysis for parties having more than 100 qty. total. I mean i want to apply set analysis such than only parties which has sum total greater than 100 should be considered. The example is as under:

Cust. : Product : Qty. : Rate

A : PSC : 50 : 200

A : PSC : 60 : 180

A : OPC : 50 : 220

B : PSC : 40 : 195

B : PSC : 80 : 205

B : OPC : 50 : 225

C : PSC : 40 : 195

C : PSC : 30 : 200

C : OPC : 50 : 230

I wanted to find correlation of Qty. & rate for PSC product for only the customer which has total greater than 100 i.e. cust A(110 MT) & B (120 MT), for PSC product i am using set analysis as {$< Product={'PSC'}>} but what should i use for customer greater than 100 to be selected in set analysis, so that it gives result for only customer A & B for product PSC.

Thank you,

Ashish

Re: Set analysis for Customer having total greater than a value

Kushal_Chawda — Tue, 01 Sep 2020 11:32:06 GMT

try below

{$< Product={'PSC'},Cust={"=sum(Qty)>100"}

Re: Set analysis for Customer having total greater than a value

Learner — Tue, 01 Sep 2020 12:11:48 GMT

Dear Kushji,

It is showing as "error in set modifier expression" are we missing any "{" (curly) bracket????

Re: Set analysis for Customer having total greater than a value

Kushal_Chawda — Tue, 01 Sep 2020 12:13:34 GMT

you need to put it in some function like below

Sum({$< Product={'PSC'},Cust={"=sum(Qty)>100"}>}Qty)

Re: Set analysis for Customer having total greater than a value

sunny_talwar — Tue, 01 Sep 2020 12:42:24 GMT

Or may be this

Sum({$<Product = {'PSC'}, Cust = {"=Sum({<Product = {'PSC'}>}Qty) > 100"}>} Qty)

Re: Set analysis for Customer having total greater than a value

Learner — Tue, 01 Sep 2020 13:56:58 GMT

for this example it is working and giving 230 MT. But when i applied on other example i had to give Product = PSC set analysis in cust= also.....................

Re: Set analysis for Customer having total greater than a value

Learner — Tue, 01 Sep 2020 13:57:43 GMT

Thank you very much Sunnyji...............

Re: Set analysis for Customer having total greater than a value

Learner — Tue, 01 Sep 2020 13:58:09 GMT

Thank you very much Kushji.........