Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
renjithpl
Specialist
Specialist

Set analysis query

Hi All,

I have a table like below

  

PartnerIDYearBusinessSalesPlanned Sales
A2016Storage500
A2017Storage 0
A2016Compute300
A2017Compute 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

1 Solution

Accepted Solutions
sunny_talwar

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)

View solution in original post

5 Replies
Anil_Babu_Samineni

Try this?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
renjithpl
Specialist
Specialist
Author

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

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)

View solution in original post

renjithpl
Specialist
Specialist
Author

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.