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

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

Tue, 15 Dec 2015 07:31:58 GMT

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

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

Anonymous — Tue, 15 Dec 2015 07:39:47 GMT

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

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

settu_periasamy — Tue, 15 Dec 2015 07:44:12 GMT

May be try this..

for your previous week

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