Qlik Community

Qlik Sense App Development

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

joey_lutes
Contributor

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(GetSelectedCount(ReferenceDate)=1, aggr(Only({$<$(vDim).VerifiedDate={">=ReferenceDate"}>}$(vDim).id),$(vDim).id), aggr(Only({$<$(vDim).VerifiedDate={'$(=vLoad)'}>}$(vDim).id),$(vDim).id))

If I split it out, the last condition works.

aggr(Only({$<$(vDim).VerifiedDate={'$(=vLoad)'}>}$(vDim).id),$(vDim).id)    //$(vLoad) is a variable pulled from load script of current date


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?





4 Replies
joey_lutes
Contributor

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:

if(GetSelectedCount(ReferenceDate)=1, aggr(Only({$<$(vDim).VerifiedDate={">=$(=ReferenceDate)"}, $(vDim).AddedDate={"<=$(=ReferenceDate)"}>}$(vDim).id),$(vDim).id), aggr(Only({$<$(vDim).VerifiedDate={'$(=vLoad)'}>}$(vDim).id),$(vDim).id))

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

If a date is selected,

aggr(Only({$<$(vDim).VerifiedDate={">=$(=ReferenceDate)"}, $(vDim).AddedDate={"<=$(=ReferenceDate)"}>}$(vDim).id),$(vDim).id)

does indeed produce intended rows, regardless of ReferenceDate selected.

If no date is selected,

aggr(Only({$<$(vDim).VerifiedDate={'$(=vLoad)'}>}$(vDim).id),$(vDim).id)

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,


Only({$<$(vDim).VerifiedDate={">=$(=ReferenceDate)"}, $(vDim).AddedDate={"<=$(=ReferenceDate)"}>}$(vDim).id),


Only({$<$(vDim).VerifiedDate={'$(=vLoad)'}>}$(vDim).id))

or this if you need your Aggr()

if(GetSelectedCount(ReferenceDate)=1,


Only({$<$(vDim).VerifiedDate={">=$(=ReferenceDate)"}, $(vDim).AddedDate={"<=$(=ReferenceDate)"}>} aggr(Only({$<$(vDim).VerifiedDate={">=$(=ReferenceDate)"}, $(vDim).AddedDate={"<=$(=ReferenceDate)"}>}$(vDim).id),$(vDim).id)),


Only({$<$(vDim).VerifiedDate={'$(=vLoad)'}>} aggr(Only({$<$(vDim).VerifiedDate={'$(=vLoad)'}>}$(vDim).id),$(vDim).id)))

joey_lutes
Contributor

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

joey_lutes
Contributor

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

    aggr(Only({$<$(vDim).VerifiedDate={'$(=vLoad)'}>}$(vDim).id),$(vDim).id))

This is functional for now and at least buy me time to troubleshoot more and report it.

Calculated Dimensions should NOT be made this complicated.  Standard expressions should be allowed.

Weird stuff,  Maynard.

Community Browser