Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
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
]
;
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
Its working fine thank you and now i want to calculate the weekly sales from this,how to calculate weekly sales?
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;
thank you
Thanks for your suggestion Boris