Last 5 Days Sales

[sonu8072]

Hi QV Experts!

I have been trying to wrap my head around this for quite a while now with no success. I have Date, Day, Week, Month and Year defined in the script and want to see 1st 5 Days Sales, In-Between Sales and Last 5 Days Sales in a Pivot Table. But I have been successful in only getting the 1st 5 Days Sales using SUM({<Day={"<=5"}>}Sales), and can get In-Between Sales as a remainder but I am not getting Last 5 Days Sales using -

SUM({< Day={"=(vLastDayofMonth)"}>}Sales_M2) where vLastDayofMonth is defined as MAX(AGGR(Day,Month))

Appreciate the help in advance.

[Mark_Little]

Hi

Normally for things like this i like to flag them in script, it makes the set analysis much easier. try something like this.
IF(TempDate <= TODAY(1) AND TempDate >= (TODAY(1)-5),1) AS F_ROLLING_5,

Then
SUM({<F_ROLLING_5={1}>}Sales)

Mark

[sonu8072]

Thanks Mark! I think I was not able to explain my problem properly, enclosed is an excel table [desired visualization] for better clarification.

[kjhertz · Accepted solution]

Making a flag in the script is a good solution unless the users wish to compare periods dynamically. If you have a CalendarDate where the user makes the selection you can filter the expressions using min / max on your Date field. Try the following script and expressions in a test document:

test:
Load
date(today()+RowNo()) as CalendarDate,
date(today()+RowNo()) as Date,
floor(Rand()*100)+1 as Sales
AutoGenerate 30;

Last 5 days sales:
=sum({<Date={">$(=date(max(Date)-5))"}>*<Date={"<=$(=max(Date))"}>}Sales)

First 5 days sales:
=sum({<Date={">=$(=min(Date))"}>*<Date={"<$(=date(min(Date)+5))"}>}Sales)

Middle sales:
=sum({<Date={"<$(=date(max(Date)-5))"}>*<Date={">$(=date(min(Date)+5))"}>}Sales)