weekly

[santhosh_k_n]

Hi All,

I need to present sales on weekly(last 8 weeks) basis, but the sum of sales should be taken on last day of each week.

eg:

from date: monsthsatrt(today()){01-05-2014} and todate=today()-1{06-05-2015}

week no	date/day	sales	weekly sales
wk18	01-05-2014/Fri	100
wk18	02-05-2014/Sat	150
wk18	03-05-2014/sun	175	175
wk19	04-05-2014/Mon	200
. . .
wk19	10-05-2014/Sun	250	250

Required chart as:

Please guide me.

[nirav_bhimani]

Hi

Try this Expression.

=Sum({< CalendarDate={">=$(=WeekStart(Today(),-7))<=$(=Date(Today()-1))"}, Year=, Month=, Quarter=>} Sales)

add the week as Dimension.

Regards,

Nirav Bhimani

[santhosh_k_n]

Hi Nirav,

Thanks for the response.

But the above expression gives sum of sales for whole last weeks but not for last 8 weeks.

and as i mentioned sum of sales should be calculated on last day of the week

[nirav_bhimani]

Hi,

Kindly find the attached solution.

Regards,

Nirav Bhimani

[santhosh_k_n]

Hi Nirav,

This is not exactly what i needed.

I need to show in chart sales happened on last day of week.

In your example, For week 11, 15th is the last day of week and sales on 15th is 100.

so in chart i need to present for wk11 values in chart as 100 but not 450(sum of sales of all other days in week )

Hope u got this

[senpradip007]

Try to execute this script:

DateRange:
LOAD
Max([Date of issue]) As MaxDate,
Min([Date of issue]) As MinDate
Resident FactTable;

LET vMinnDate = Peek('MinDate',0,'DateRange');
LET vMaxxDate = Peek('MaxDate',0,'DateRange');

DROP Table DateRange;

Calendar:
LOAD
Year($(vMinnDate) + RecNo() - 1) &'-'& Week($(vMinnDate) + RecNo() - 1) &'-'& Day($(vMinnDate) + RecNo() - 1) AS Key,
Date($(vMinnDate) + RecNo() - 1) AS Date,
Month($(vMinnDate) + RecNo() - 1) AS Month,
Year($(vMinnDate) + RecNo() - 1) AS Year,
Week($(vMinnDate) + RecNo() - 1) AS Week,
'Q' & Ceil(Month($(vMinnDate) + RecNo() - 1)/3) AS Quarter,
Day($(vMinnDate) + RecNo() - 1) As Day

AutoGenerate ($(vMaxxDate) - $(vMinnDate) + 1);

Temp:
NoConcatenate
LOAD
Year,
Week,
Max(Day) As MaxD,
1 As MaxWeekDay
Resident Calendar

Group By Year, Week;

Left Join(Calendar)
LOAD
Year&'-'&Week&'-'&MaxD As Key,
MaxWeekDay
Resident Temp;

DROP Table Temp;

In Expressions:

Sum(({<CalendarDate={">=$(=WeekStart(Today(),-7))<=$(=Date(Today()-1))"}, MaxWeekDay={1}>} Sales)

[nirav_bhimani]

Hi,

Kindly find the updated solution.

Regards,

Nirav Bhimani

[santhosh_k_n]

Hi All,

Thanks for ur responce.

The expression "Sum(({<CalendarDate={">=$(=WeekStart(Today(),-7))<=$(=Date(Today()-1))"}, MaxWeekDay={1}>} Sales)" is not matching with my requirement. Instead i tried as below,

1. create 8 variables with last day of the week for 8 weeks

          eg: last_1week: MakeWeekDate(year(today()),week(today()-1),6)

               last_2week: MakeWeekDate(year(today()),week(today()-2),6)

               - - -

               last_8week: MakeWeekDate(year(today()),week(today()-8),6)

2. expression:

sum( {<date= {$last_1week,$last_2week------,$last_8week}>} sales)