Set Analysis - last 7 days

[simonb2013]

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)

[sunny_talwar]

Try this (you are missing the ending >}

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

[simonb2013]

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

Original code has it, and problem remains

[felipedl · Accepted solution]

Hi Simon,

See attached QVW to see if it helps you.

Regards,

Felipe.

[sunny_talwar]

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

[simonb2013]

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]

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 .