Set analysis query

[renjithpl]

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

[Anil_Babu_Samineni]

Try this?

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

[renjithpl]

I tried, it doesnt give the result.

[sunny_talwar]

May be this

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

[sunny_talwar · Accepted solution]

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)

[renjithpl]

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.