Skip to main content
Announcements
The New Qlik Learning Experience is Here! GET STARTED
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Current (max) Date vs Previous Week Turnover -SET Analysis

Dear All,

our requirement is to show the max date Turnover in one column and Previous Week Turnover in second column.

below is the set analysis which i have written for same.

1) Max Date Turnover : (DWHDateOnly is a Date Field, DWHTurnoverByDealer is Turnover)

sum({<DWHDateOnly={"$(=date(max(DWHDateOnly),'dd-MMM-yyyy'))"}>} DWHTurnoverByDealer)

2) Previous Week Turnover : (DWHWeekStart is a WeekStartDate field )

sum({<DWHWeekStart={"$(=date(max(DWHWeekStart)-7,'dd-MMM-yyyy'))"}>} DWHTurnoverByDealer)

When i am not selecting any date from DWHDateOnly then set analysis working fine, but when i select any date from DWHDateOnly then the previous Week set analysis get failed due to not able to fetch previous  WeekStartDate due to limit of date selection.

Kindly let me know how can we resolve this.

for you ready reference i am sharing you a sample data along with the QVW.

DATE1 is equivalent to  DWHDateOnly and WEEKSTARTDATE is equivalent to DWHWeekStart.

kindly note this is just a sample data the original data have more columns and dimensions.

thanks & regards,

Jitendra Kumar Vishwakarma

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

try with bypassing the Date fileds like this:

=sum({<YearField=,DWHDateOnly=,DWHDateOnly={"$(=date(max(DWHDateOnly),'dd-MMM-yyyy'))"}>} DWHTurnoverByDealer)

=sum({<YearField=,DWHDateOnly=,DWHWeekStart=,DWHWeekStart={"$(=date(max(DWHWeekStart)-7,'dd-MMM-yyyy'))"}>} DWHTurnoverByDealer)

also make sure format of both DWHDateOnly & DWHWeekStart should be dd-MMM-yyyy

View solution in original post

2 Replies
Anonymous
Not applicable
Author

try with bypassing the Date fileds like this:

=sum({<YearField=,DWHDateOnly=,DWHDateOnly={"$(=date(max(DWHDateOnly),'dd-MMM-yyyy'))"}>} DWHTurnoverByDealer)

=sum({<YearField=,DWHDateOnly=,DWHWeekStart=,DWHWeekStart={"$(=date(max(DWHWeekStart)-7,'dd-MMM-yyyy'))"}>} DWHTurnoverByDealer)

also make sure format of both DWHDateOnly & DWHWeekStart should be dd-MMM-yyyy

settu_periasamy
Master III
Master III

May be try this..

for your previous week

=sum({1<WEEKSTARTDATE={"$(=date(max(WEEKSTARTDATE)-7,'dd-MMM-yyyy'))"}>} TURNOVER)