topic Re: Set analysis issue in QlikView

Set analysis issue

triciagdaly — Wed, 06 Jul 2016 21:43:59 GMT

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!

Re: Set analysis issue

sunny_talwar — Wed, 06 Jul 2016 21:50:04 GMT

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)), ' ')

Re: Set analysis issue

triciagdaly — Wed, 06 Jul 2016 21:57:40 GMT

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?

Re: Set analysis issue

sunny_talwar — Wed, 06 Jul 2016 21:59:20 GMT

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