Qlik Community

Qlik Sense App Development

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

simonb2013
New Contributor III

Set Analysis - last 7 days

So I have a heap of transactions each with [CalDate]   - a date formatted as YYYY-MM-DD

I want to count the number of transactions the occurred in the last 7 days ; or be to precise, the last "loaded" 7 days.

My formula wasn't working, so I've tried to simplify it for testing.
Now I am just trying to create a set of the desired data, and return the earliest date.

I can't even get it to do that.

Broken down :

  • The last date loaded in my dataset, regardless of dimension:
    • Max(Total [CalDate])

(This works)

  • 7 Days previous
    • Date(Max(Total [CalDate])-7)

(This works)

  • Final formula : (should bring back the earliest date present within 7 day dataset)
    • Min( { $< [CalDate] = {">= $(=Date(Max(Total [CalDate])-7))" }>} [CalDate])

(This just produces NULL)

QS.PNG

1 Solution

Accepted Solutions
felipedl
Valued Contributor III

Re: Set Analysis - last 7 days

Hi Simon,

See attached QVW to see if it helps you.

Regards,

Felipe.

6 Replies
MVP
MVP

Re: Set Analysis - last 7 days

Try this (you are missing the ending >}

Min({$<[CalDate] = {">= $(=Date(Max([CalDate])-7, 'YYYY-MM-DD'))"}>} [CalDate])

simonb2013
New Contributor III

Re: Set Analysis - last 7 days

Cheers Sunny, I've edited in the post, small (but important) typo.

Original code has it, and problem remains

felipedl
Valued Contributor III

Re: Set Analysis - last 7 days

Hi Simon,

See attached QVW to see if it helps you.

Regards,

Felipe.

MVP
MVP

Re: Set Analysis - last 7 days

Did you check with the other changes? Like removing total because it is unnecessary and adding the date format?

simonb2013
New Contributor III

Re: Set Analysis - last 7 days

Thanks Felip.

Your formula did work.

However, I was then curious as to why mine did not.

I started doing some comparisons and found this:

If I leave a space between the ">="  and the "$"  of my nested expression, my formula fails completely.

If I remove the space, than it all works fine.

  • ">= $
  • ">=$

With hindsight, I guess it is parsing a space to the start of my date string !?

Is this something that I should have known ??

I don't recall seeing in documentation - but I can sometimes read a bit too fast for my own good !!

Again, hindsight is a wonderful thing.

felipedl
Valued Contributor III

Re: Set Analysis - last 7 days

Hi Simon,

Could be as you said a parsing error due to the space, just tried it here with the same result you got.

Normally i just type without spaces and this situation never occured to me, but its nice to know .