Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
ananyaghosh
Creator III
Creator III

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

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;

2 Replies
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


talk is cheap, supply exceeds demand
ananyaghosh
Creator III
Creator III
Author

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.