topic Re: Distributing Sales across Days in QlikView

Distributing Sales across Days

Vinothishere — Sun, 21 Jun 2015 09:33:52 GMT

Hi all,

I have a monthly sales data and i want to distribute the sales across days.For example: If sales for Jan month is 5000,I want to distribute it across 31 days likewise for all the months i have to distribute.

How to do that??

Thanks in Advance

Regards,

Re: Distributing Sales across Days

Kushal_Chawda — Sun, 21 Jun 2015 10:24:00 GMT

The best way to do it is to create the Drill Down group

Go to chart Properties-> Dimension-> Edit group and create a Drill Down group. Add Month and Day dimension in group.

Use this drill down group as dimension of chart.

It will create the month wise chart and when you click on any Bar of month it will drill down to day level

Re: Distributing Sales across Days

swuehl — Sun, 21 Jun 2015 10:45:05 GMT

You can do it like this:

LOAD Month, 
  MonthlySales, 
  Date(Month+iterno()-1) as Date,
  MonthlySales / NumDaysInMonth as DailySales
while iterno() <= NumDaysInMonth;
LOAD date#(Month,'YYYY-MMM') as Month,
  Sales as MonthlySales,
  day(monthend(date#(Month,'YYYY-MMM'))) as NumDaysInMonth
INLINE [
Month, Sales
2015-Jan, 2000
2015-Feb, 2000
2015-Mar, 2500
]
;

Re: Distributing Sales across Days

Sun, 21 Jun 2015 10:56:46 GMT

Hi Vinoth,

sounds like you want to make sure that e.g. average values include those days , which have Zero sales?

Like swuehl suggested, you then need a loop to create a structure you can relate your data to.

Michael Tarallo has uploaded an excellent Video on master calendars Understanding the Master Calendar (video) which I recommend viewing.

I would also suggest left joining a table with public holidays to your master calender, so you can flag out these dates if needed.

best regards,

Boris

Re: Distributing Sales across Days

Vinothishere — Sun, 21 Jun 2015 13:24:28 GMT

Its working fine thank you and now i want to calculate the weekly sales from this,how to calculate weekly sales?

Re: Distributing Sales across Days

swuehl — Sun, 21 Jun 2015 13:49:24 GMT

Now that you got sales on a daily basis, you link your Date field with a calendar, and use the calendar field as dimension to group your sales date.

Sales:
LOAD //Month, 
  //MonthlySales, 
  Date(Month+iterno()-1) as Date,
  MonthlySales / NumDaysInMonth as DailySales
while iterno() <= NumDaysInMonth;
LOAD date#(Month,'YYYY-MMM') as Month,
  Sales as MonthlySales,
  day(monthend(date#(Month,'YYYY-MMM'))) as NumDaysInMonth
INLINE [
Month, Sales
2015-Jan, 2000
2015-Feb, 2000
2015-Mar, 2500
]
;


Calendar:
LOAD Date,
  MonthName(Date) as MonthName,
  WeekYear(Date) & '-' & Week(Date) as Week,
  Year(Date) as Year
RESIDENT Sales;

Re: Distributing Sales across Days

Vinothishere — Tue, 23 Jun 2015 12:50:14 GMT

thank you

Re: Distributing Sales across Days

Vinothishere — Tue, 23 Jun 2015 14:07:30 GMT

Thanks for your suggestion Boris