Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
triciagdaly
Contributor III
Contributor III

Set analysis issue

I have an expression using Set Analysis that I'm struggling with.  Referring to the attachment 'Set Analysis' shows the results of my chart,   I've displayed the detail data of Receipt_Date, Effective_Date and Cost Roll Period for your reference. 

I am needing to show the sum of RECEIPT_APPROVED_QUANTITY where Cost Roll Period = '1' and Receipt Date <= Effective Date and the Receipt Year = vRollCostYear.  I have verified with a text object the vRollCostYear = 2015.  The attachment 'Set Analysis 2' shows the end result format..

The only values that should be displayed are highlighted in yellow.  The remaining values should be null.  I"m not sure why the other values are being displayed as the Receipt Date is not less than the Effective Date.

My expression is:

=if (dimensionality()<>0,

   sum( {<[Cost Roll Period]={'1'},RECEIPT_DATE={"<=$(EFFECTIVE_DATE)"},Receipt_Year={$(vRollCostYear)}>}RECEIPT_APPROVED_QUANTITY)

   ,' ')

I appreciate any help... been struggling with this all day.  Thanks!

3 Replies
sunny_talwar

I think you need a field that can uniquely identify a single receipt_date and effective_date combination here. May be something along these lines

=If(Dimensionality() <> 0, Sum({<[Cost Roll Period]={'1'}, UniqueIdentifier = {"=RECEIPT_DATE <= EFFECTIVE_DATE)"}, Receipt_Year = {$(vRollCostYear)}>} RECEIPT_APPROVED_QUANTITY), ' ')

or go with the if statement option here:

=If(Dimensionality() <> 0, Sum({<[Cost Roll Period]={'1'}, Receipt_Year = {$(vRollCostYear)}>} If(RECEIPT_DATE <= EFFECTIVE_DATE, RECEIPT_APPROVED_QUANTITY)), ' ')

triciagdaly
Contributor III
Contributor III
Author

When using the If statement it's multiplying the qty *4.. and can't figure out why.. I tried the if statement earlier.  See Attachment Set Analysis 3.

I'm not sure I understand what you mean by Unique Identifier.. where and how would I establish that?

sunny_talwar

Are the dimensions used in this chart coming from more than 1 table in the backend?