topic Re: Set analysis in QlikView

Set analysis

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

I am trying to count orders under £15 pounds and I am using this set analysis but its not taking all the products line values, i think I need to add sum in set analysis ,So can i do that

=Count({<[IO.GBP Amount (ex VAT)]={"<15"}>}DISTINCT [IO.Original Order No_])

Where sum goes??

Re: Set analysis

sunny_talwar — Wed, 31 May 2017 14:00:13 GMT

May be this

=Count({<[IO.Original Order No_] = {"=Sum([IO.GBP Amount (ex VAT)]) <15"}>} DISTINCT [IO.Original Order No_])

Re: Set analysis

its_anandrjs — Wed, 31 May 2017 14:03:50 GMT

Small change in sunnys code put Distinct on correct position and check.

=Count( DISTINCT {<[IO.Original Order No_] = {"=Sum([IO.GBP Amount (ex VAT)]) < 15"}>} [IO.Original Order No_])

Regards

Anand

Re: Set analysis

madhuqliklondon — Wed, 31 May 2017 14:08:24 GMT

Hi Sunny ,

I have total count of orders , I am classifying them into under 15 and over 15, I am using the set analysis you have given , but when I add them (under 15 and over 15 ) they are coming more than total original orders count, why is that?

Re: Set analysis

sunny_talwar — Wed, 31 May 2017 14:13:50 GMT

AFAIK DISTINCT works on either side of the set analysis

Re: Set analysis

sunny_talwar — Wed, 31 May 2017 14:14:16 GMT

Can you share your app or images?

Re: Set analysis

madhuqliklondon — Wed, 31 May 2017 14:19:57 GMT

Re: Set analysis

madhuqliklondon — Wed, 31 May 2017 14:23:18 GMT

I have below expressions ,

count(distinct([IO.Sell-to Customer No_])) ,

Count(DISTINCT([IO.Original Order No_])) ,

Sum([IO.GBP Amount (ex VAT)]) ,

avg(Aggr(Sum([IO.GBP Amount (ex VAT)]),[IO.Original Order No_])) ,

Count( DISTINCT {<[IO.Original Order No_] = {"=Sum([IO.GBP Amount (ex VAT)]) < 15"}>} [IO.Original Order No_]),

Count( DISTINCT {<[IO.Original Order No_] = {"=Sum([IO.GBP Amount (ex VAT)]) > 15"}>} [IO.Original Order No_])

Re: Set analysis

sunny_talwar — Wed, 31 May 2017 14:25:19 GMT

Which expression is the one which is not working?

Re: Set analysis

madhuqliklondon — Wed, 31 May 2017 14:28:46 GMT

these two expressions

Count( DISTINCT {<[IO.Original Order No_] = {"=Sum([IO.GBP Amount (ex VAT)]) < 15"}>} [IO.Original Order No_]),

Count( DISTINCT {<[IO.Original Order No_] = {"=Sum([IO.GBP Amount (ex VAT)]) > 15"}>} [IO.Original Order No_])

not matching to this expression

Count(DISTINCT([IO.Original Order No_]))

Re: Set analysis

sunny_talwar — Wed, 31 May 2017 14:31:20 GMT

There might be few orders which are exactly 15? because your conditions are < 15 and > 15, =15 is getting excluded?

Re: Set analysis

madhuqliklondon — Wed, 31 May 2017 15:39:37 GMT

I will try with that Sunny.