topic Set analysis query in QlikView

Set analysis query

renjithpl — Wed, 16 Aug 2017 11:09:33 GMT

Hi All,

I have a table like below

PartnerID	Year	Business	Sales	Planned Sales
A	2016	Storage	500
A	2017	Storage		0
A	2016	Compute	300
A	2017	Compute		100

I need to get the sum (Sales) of 2016 where 2017 Panned sales is greater than 0 in set analysis.

My result should be 300.

THanks

Re: Set analysis query

Anil_Babu_Samineni — Wed, 16 Aug 2017 11:24:27 GMT

Try this?

Sum({<Year = {2016}, [Planned Sales] = {"=Sum([Planned Sales]) > 0"}>} Sales)

Re: Set analysis query

renjithpl — Wed, 16 Aug 2017 11:30:01 GMT

I tried, it doesnt give the result.

Re: Set analysis query

sunny_talwar — Wed, 16 Aug 2017 11:34:16 GMT

May be this

Sum({<Year = {2016}, Business = {"=Sum({<Year = {2017}>}[Planned Sales]) > 0"}>} Sales)

Re: Set analysis query

sunny_talwar — Wed, 16 Aug 2017 11:35:58 GMT

Or if this is needed by PartnerID and Business, then I would create a new field in the script like this

LOAD PartnerID,

Year,

Business,

Sales,

Planned Sales,

AutoNumber(PartnerID&'|'&Business) as Key

FROM....

and then this

Sum({<Year = {2016}, Key = {"=Sum({<Year = {2017}>}[Planned Sales]) > 0"}>} Sales)

Re: Set analysis query

renjithpl — Wed, 16 Aug 2017 11:44:47 GMT

Hi Sunny,

Many thanks for the reply, first option seems working, this is just the sample, Let me try with couple of scenarios also, the second option looks appealing to me, Let me try that too. will get back soon.

thanks.