Set Analysis For calculating Last 4 weeks total

[#1]

Hi experts,

I am stuck in writing an expression for calculating the last 4 weeks total customers.

I have a data set like below

I want to display the chart which shows me week wise total customers. But the total customers must be calculated based on last four weeks. This means result should be

Week 1 - Total Customer=4

Week 2 - Total Customer=11

Week 3 - Total Customer = 18

Week 4 - Total Customer = 25

Week 5 - Total Customer= 28 and so on

Please help me in writing the set expression for this scenario.

Appreciate your help.

[marwen_garwachi]

Hello,

you can inverse the dimensions order WEEK_IN_YEAR,CAL_YEAR

best regards

[SunilChauhan]

sum(Customer)+above(sum(Customer)) in Expression

[marwen_garwachi]

using above I think the result will be

Week 1 - Total Customer=0

Week 2 - Total Customer=11

Week 3 - Total Customer = 14

Week 4 - Total Customer = 14

Week 5 - Total Customer= 14 and so on

Don't you ?

[#5]

Thats right Marween. I need last four weeks total not the cumulative total.

Any Ideas for the expression?

[SunilChauhan]

have you tried sum with above(sum)

sum(Customer)+above(sum(Customer))

it should give  4  if above(sum(Customer)) is 0 ,else usual

[jagan]

Hi,

Try like this

LOAD
Cal_Year,

Week_In_Year,

Customers,

MakeWeekDate(Cal_Year, Week_In_Year) AS Date

FROM DataSource;

Now in set analysis try like this

=Sum({<Cal_Year=, Week_In_Year=, Date={'>=$(=WeekStart(Max(Date), -3))<=$(=WeekEnd(Max(Date)))'}>} Customers)

Hope this helps you.

Regards,

Jagan.

[jagan]

Hi,

If you are using Straight table, then try like this

Chart Properties -> Expression-> Click on Expression-> Select Accumulate checkbox and give 4 steps back.

Regards,

Jagan.

[#9]

Hi Ruchika,

In your chart use following steps :

Dimension 1 - WEEK_IN_YEAR

Dimension 2 - CAL_YEAR

Expression -    Sum(Customers)

Check button Full Accumulation in Expression tab

Thanks,

Prakash

[marwen_garwachi]

Did you tried to inverse dimensions ? (sum(Customers) in expression and check full accumulation button )