Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Last 5 Days Sales

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.

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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)

View solution in original post

3 Replies
Highlighted
Partner
Partner

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
Highlighted
Contributor III
Contributor III

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

Highlighted
Partner
Partner

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)

View solution in original post