Qlik Community

App Development

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

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
simonb2013
Creator
Creator

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

Hi Simon,

See attached QVW to see if it helps you.

Regards,

Felipe.

View solution in original post

6 Replies
sunny_talwar

Try this (you are missing the ending >}

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

simonb2013
Creator
Creator
Author

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

Original code has it, and problem remains

felipedl
Partner
Partner

Hi Simon,

See attached QVW to see if it helps you.

Regards,

Felipe.

View solution in original post

sunny_talwar

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

simonb2013
Creator
Creator
Author

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

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 .