topic Re: Set Analysis - last 7 days in App Development

Set Analysis - last 7 days

simonb2013 — Tue, 30 May 2017 11:34:26 GMT

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)

Re: Set Analysis - last 7 days

sunny_talwar — Tue, 30 May 2017 11:40:33 GMT

Try this (you are missing the ending >}

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

Re: Set Analysis - last 7 days

simonb2013 — Tue, 30 May 2017 11:43:46 GMT

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

Original code has it, and problem remains

Re: Set Analysis - last 7 days

felipedl — Tue, 30 May 2017 11:45:46 GMT

Hi Simon,

See attached QVW to see if it helps you.

Regards,

Felipe.

Re: Set Analysis - last 7 days

sunny_talwar — Tue, 30 May 2017 11:51:26 GMT

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

Re: Set Analysis - last 7 days

simonb2013 — Tue, 30 May 2017 12:12:12 GMT

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.

Re: Set Analysis - last 7 days

felipedl — Tue, 30 May 2017 12:19:29 GMT

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 .