6 Replies Latest reply on Jul 28, 2016 3:15 AM by Shaji Mathew

Selecting last 3 dates from a list of dates dynamically

Shaji Mathew Jul 27, 2016 6:38 PM

Hi,

I have a list of dates against sales as below.

Date	Sales
27/07/2017	1000
26/07/2017	2000
25/07/2017	5000
15/07/2017	1000
14/7/2017	10000
13/07/2017	8000
12/7/2017	2500
11/7/2017	7500
8/7/2017	9000
7/7/2017	12000
6/7/2017	750
5/7/2017	500
4/7/2017	6000
1/7/2017	15000
30/06/2017	12000
29/06/2017	8000

I need to display the last 3 days details and corresponding sum and average.

I used MAX to trace back the last 3 days but seems not working.

Any idea how to resolve this.

Thanks.

1. Re: Selecting last 3 dates from a list of dates dynamically

Mahfooj Khan Jul 28, 2016 12:29 AM (in response to Shaji Mathew)

You can try something like this. Drag this calc field in filters shelf and filter 1 to 3.
Hope this help.

Mahfooj
2 of 2 people found this helpful
Like Show 1 Likes(1)

Actions
2. Re: Selecting last 3 dates from a list of dates dynamically

Shaji Mathew Jul 28, 2016 1:21 AM (in response to Mahfooj Khan)

Thanks very much. It did work.
But when I move to Columns pane, it is not working.
Even sometime it work, when additional information added, it expand to all.

Thanks..
Like Show 0 Likes(0)

Actions
3. Re: Selecting last 3 dates from a list of dates dynamically

Mark Fraser Jul 28, 2016 2:07 AM (in response to Shaji Mathew)
Hi Shaji

You can do it multiple ways...
Something like this is also possible - working example attached, suing Superstore (v9.3.5)

logical test if date is last 3 days (dates must be pre-sorted by date)
LAST() >= 0 and LAST()<3

That will return true for the last 3 days, then use that calc, to leverage your sales...
sum = IF [last 3 days] THEN SUM([Sales]) END
avg = IF [last 3 days] THEN AVG([Sales]) END

Hope that helps!

Cheers
Mark

212020.twbx 300.7 KB

2 of 2 people found this helpful
Like Show 2 Likes(2)

Actions
4. Re: Selecting last 3 dates from a list of dates dynamically

Shaji Mathew Jul 28, 2016 2:32 AM (in response to Mark Fraser)

Thanks very much..
I have resolved the issue .
Thanks once again to both Mahfooj & Mark.
Like Show 2 Likes(2)

Actions
5. Re: Selecting last 3 dates from a list of dates dynamically

Mark Fraser Jul 28, 2016 3:00 AM (in response to Shaji Mathew)

Shaji, out of interest, which method did you use?
Like Show 1 Likes(1)

Actions
6. Re: Selecting last 3 dates from a list of dates dynamically

Shaji Mathew Jul 28, 2016 3:15 AM (in response to Mark Fraser)

I used your solutions and used it as a filter.
thanks
1 of 1 people found this helpful
Like Show 2 Likes(2)

Actions