Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am preparing a Qlikview document. Can you help me for previous month also?
Also Can I add together previous month, previous date's and week's calculation in one as of table ?
After taking help from community I have modified the script as :
tmpAsOfCalendar:
Load distinct [Order Date]
Resident [Order Date Data] ;
Join (tmpAsOfCalendar)
Load [Order Date] as AsOfDate
Resident tmpAsOfCalendar ;
[As-Of Calendar]:
Load *,
If(DayDiff=0,'Current Day',If(DayDiff=1,'Previous Day')) as FlagDate,
If(WeekDiff=0,'Current Week',If(WeekDiff=1,'Previous Week')) as FlagWeek,
If(MonthDiff=0,'Current Month',If(MonthDiff=1,'Previous Month')) as FlagMonth,
If(YearDiff=0,'Current Year',If(YearDiff=1,'Previous Year')) as FlagYear;
Load
[Order Date],
[AsOfDate],
[AsOfDate] - [Order Date] as DayDiff,
Round((WeekStart(AsOfDate)-WeekStart([Order Date]))/7) as WeekDiff,
Round((AsOfDate-[Order Date])*12/365.2425) as MonthDiff,
Year(AsOfDate)-Year([Order Date]) as YearDiff
Resident
tmpAsOfCalendar
Where
AsOfDate >= [Order Date];
Drop Table tmpAsOfCalendar;
In the [Order Date Data] table change the line
date([Order Date], 'YYYY-MM') as [Month ID],
to
date(MonthStart([Order Date]), 'YYYY-MM') as [Month ID],
It's possible to create a table with all three as-of fields, but it would have to be based on the Order Date field and could get a lot bigger than your three separate tables.
tmpAsOfCalendar:
Load distinct [Order Date]
Resident [Order Date Data] ;
Join (tmpAsOfCalendar)
Load [Order Date] as AsOfDate
Resident tmpAsOfCalendar ;
[As-Of Calendar]:
Load *,
If(DayDiff=0,'Current Day',If(DayDiff=1,'Previous Day')) as FlagWeek,
If(WeekDiff=0,'Current Week',If(WeekDiff=1,'Previous Week')) as FlagWeek,
If(MonthDiff=0,'Current Month',If(MonthDiff=1,'Previous Month')) as FlagMonth,
;
Load
[Order Date],
[AsOfDate],
AsOfDate - [Order Date] as DayDiff,
Round((WeekStart(AsOfDate)-WeekStart([Order Date]))/7) as WeekDiff
Round((AsOfDate-[Order Date])*12/365.2425) as MonthDiff,
Year(AsOfDate)-Year(Date) as YearDiff
Resident
tmpAsOfCalendar
Where
AsOfDate >= [Order Date];
Drop Table tmpAsOfCalendar;
Hi,
I am using your code with little bit modified. But I am getting the wrong result as shown below:
So whenever I add AsOfDate', 'MonthID as dimensions and add the following expressions it gives me wrong results:
1. sum({<FlagDate={'Current Day'}>}Sales) - for current date
2. sum({<FlagDate={'Previous Day'}>}Sales) - for previous day date
3. sum({<FlagMonth={'Current Month'}>}Sales) - for current month
4. sum({<FlagMonth={'Previous Month'}>}Sales) - for previous month
5. sum({<WeekDiff={0}>}Sales) - for current week
6. sum({<WeekDiff={1}>}Sales) - for previous week
So in my chart with title: 'Sales With Order Date' gives me wrong result for current month and previous month, as because they show the 0 values there.
So please help me to modify my script or chart expression !to overcome this problem.