4 Replies Latest reply: Dec 15, 2016 3:58 PM by Joey Lutes

# Calculated Dimension Comparing Dates Using Set Analysis

I have an expression that's not working as intended - was hoping someone could shed some light please:

This is in a straight table, and it's intended to show results in 2 scenarios

a)  when a date is selected, show data where the VerifiedDate = the date selected (ReferenceDate)

b)  if no date is selected, show data where the VerifiedDate = today's date (\$(vLoad)

In either scenario, the resulting rows should be ids selected from the VerifiedDate being compared to 1 date only.

If I split it out, the last condition works.

If I use the first condition, it does not, however - produces incorrect values.

aggr(Only({\$<\$(vDim).VerifiedDate={">=ReferenceDate"}>}\$(vDim).id),\$(vDim).id) //ReferenceDate is a date field.  Format verified.

I'm pretty sure my issue lies in here  {">=ReferenceDate"}  but I ran out of combinations to try.

Thoughts?

• ###### Re: Calculated Dimension Comparing Dates Using Set Analysis

I think I'm getting closer.  The original statement I posted isn't quite accurate.  The correct statement is:

I've verified that the individual components of the 'if' statement both produce correct results now:

If a date is selected,

does indeed produce intended rows, regardless of ReferenceDate selected.

If no date is selected,

also produces intended rows.

However, when I put the two in an 'if' statement, it stops working IF any date except the first one is selected.  The 'else' portion is functioning.

ANY ideas as to why I might be seeing that behavior?

• ###### Re: Calculated Dimension Comparing Dates Using Set Analysis

Not sure why you need Aggr() here, can you try this:

if(GetSelectedCount(ReferenceDate)=1,

or this if you need your Aggr()

if(GetSelectedCount(ReferenceDate)=1,

• ###### Re: Calculated Dimension Comparing Dates Using Set Analysis

The only reason I had AGGR in there was because I couldn't get ONLY to work on its own.

I did try both snippets, and both produced 'Invalid Dimension' errors.  I don't see any glaring issues there.

So I can try each of the conditions independently.

When I separate them out and try the ONLY conditions on their own, each produces the same 'Invalid Dimension' error.

Thanks,

Joey

• ###### Re: Calculated Dimension Comparing Dates Using Set Analysis

So I've resorted to using a nested 'if' statement.  I've decided it must be a bug in Qlik's code - because both conditions work perfectly on their own, and when combined the results are wonky.

if(GetSelectedCount(ReferenceDate)=1,

if([\$(vDim).VerifiedDate]>= ReferenceDate and [\$(vDim).AddedDate] <= ReferenceDate, [\$(vDim).id]),