Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Vinothishere
Contributor III
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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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
Kushal_Chawda

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

swuehl
MVP
MVP

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

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

Vinothishere
Contributor III
Contributor III
Author

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

swuehl
MVP
MVP

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;

Vinothishere
Contributor III
Contributor III
Author

thank you

Vinothishere
Contributor III
Contributor III
Author

Thanks for your suggestion Boris