Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
SDT
Contributor III
Contributor III

Sum Prior year Measure only for products sold in the current year?

Hi,

Using the following fields in the model: FiscalYear, InvoiceAmount, and ProductID I need to write a sum with set analysis that will sum all the InvoiceAmt from the prior fiscal year but only for products sold in the current fiscal year.

I thought this might do it:

SUM({$<FiscalYear={"FY20"}>*$<ProductID=P({FiscalYear={"FY21"}, InvoiceAmount={">0"} ProductID)>} InvoiceAmount)

It is not producing the expected results since there are rows in the table for a productID with zero InvoiceAmount in the current year and >0 in the prior year.

1 Solution

Accepted Solutions
GaryGiles
Specialist
Specialist

I don't think you need to use an intersection in your set analysis.  Also, the P() function to be bracketed with {< >}.  Try this:

SUM({$<FiscalYear={"FY20"},ProductID=P({<FiscalYear={"FY21"}, InvoiceAmount={">0"}>} ProductID)>} InvoiceAmount)

Also, not sure that you need InvoiceAmount={">0"}, but you can test that.

View solution in original post

1 Reply
GaryGiles
Specialist
Specialist

I don't think you need to use an intersection in your set analysis.  Also, the P() function to be bracketed with {< >}.  Try this:

SUM({$<FiscalYear={"FY20"},ProductID=P({<FiscalYear={"FY21"}, InvoiceAmount={">0"}>} ProductID)>} InvoiceAmount)

Also, not sure that you need InvoiceAmount={">0"}, but you can test that.

View solution in original post