topic Calculated Dimension Comparing Dates Using Set Analysis in App Development

Calculated Dimension Comparing Dates Using Set Analysis

joey_lutes — Wed, 14 Dec 2016 06:10:27 GMT

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?

Re: Calculated Dimension Comparing Dates Using Set Analysis

joey_lutes — Thu, 15 Dec 2016 15:54:34 GMT

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

sunny_talwar — Thu, 15 Dec 2016 16:52:42 GMT

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

Re: Calculated Dimension Comparing Dates Using Set Analysis

joey_lutes — Thu, 15 Dec 2016 17:05:29 GMT

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

joey_lutes — Thu, 15 Dec 2016 20:56:57 GMT

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.