Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
vinoth25593
New Contributor III

Distributing Sales across Days

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,

VR

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Distributing Sales across Days

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;

View solution in original post

7 Replies

Re: Distributing Sales across Days

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

Untitled.jpg

MVP
MVP

Re: Distributing Sales across Days

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

]

;

Not applicable

Re: Distributing Sales across Days

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

vinoth25593
New Contributor III

Re: Distributing Sales across Days

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

MVP
MVP

Re: Distributing Sales across Days

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;

View solution in original post

vinoth25593
New Contributor III

Re: Distributing Sales across Days

thank you

vinoth25593
New Contributor III

Re: Distributing Sales across Days

Thanks for your suggestion Boris