need help for attached QVW document for calculating Previous month, week, and year sales

[ananyaghosh]

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;

[Gysbert_Wassenaar]

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;

adapted from

[ananyaghosh]

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.