Storing set expressions into variables

Anonymous — Wed, 29 Jul 2015 07:04:15 GMT

I have the following calendar in QlikView:

QuartersMap:
MAPPING LOAD
RowNo() as Month,
'Q' & Ceil (RowNo()/3) as Quarter
AUTOGENERATE (12);

Temp:
LOAD
min(Date(Floor([DateDispensed]))) as minDate,
max(Date(Floor([DateDispensed]))) as maxDate
Resident [Main Data];

LET varMinDate = Num(Peek('minDate', 0, 'Temp'));
LET varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD DISTINCT
$(varMinDate) + IterNo()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load *,
AutoNumber(Year & Quarter, 'QuarterID')          as [QuarterID],
AutoNumber(Period, 'PeriodID')                           as [PeriodID],
AutoNumber(Month, 'MonthID')                                    as [MonthID],
AutoNumber(Week, 'WeekID')                                      as [WeekID]
;
LOAD DISTINCT
Date(Floor(TempDate)) as [DateDispensed],
Year(TempDate) * 100 + Month(TempDate)            as [Period],
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
YearToDate(TempDate)*-1 as CurYTDFlag,
YearToDate(TempDate, -1)*-1 as LastYTDFlag,
InYear(TempDate, MonthStart($(varMaxDate)),-1) as RC12,
Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', Month(TempDate), Null()) as Quarter,
Week(Weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay,
Timestamp(Frac(TempDate)) as TimeDispensed
Resident TempCalendar
Order By TempDate asc;
DROP Table TempCalendar;

[Master Calendar]:
LOAD *,
Year as Last4Year
Resident [MasterCalendar] Where Year >= Year(Today()) - 3 and Year <= Year(Today());
Drop Table MasterCalendar;

////--- Remove the temporary variables
LET varMinDate = Null();
LET varMaxDate = Null();

I am trying to have variable current 12 weeks and previous 12 weeks which i have set as follows

vSetPreviousNWeek =

WeekID = {$(=Max(WeekID) - $1)},
Year = ,
Quarter = ,
Month = ,
Week = ,
Period =

and vSetPreviousNWeekLastYear =

WeekID = {$(=Max(WeekID) - $1)},
Year = {$(=Max(Year) - 1)},
Quarter = ,
Month = ,
Week = ,
Period =

Can anyone assist

Re: Storing set expressions into variables

Gysbert_Wassenaar — Wed, 29 Jul 2015 09:28:16 GMT

Dollar expansion also happens in the script so Qlikview tries to evaluate $(=Max(WeekID) - $1).

Try this:

LET vSetPreviousNWeek =

'WeekID = {$' & '(=Max(WeekID) - $1)},
Year = ,
Quarter = ,
Month = ,
Week = ,
Period ='

;

Re: Storing set expressions into variables

Anonymous — Wed, 29 Jul 2015 11:30:22 GMT

Thanks, Gysbert i will try it out

Regards.

Chris

topic Storing set expressions into variables in QlikView

Storing set expressions into variables

Re: Storing set expressions into variables

Re: Storing set expressions into variables