Drill Down: Week level showing data for whole week

[#1]

Hi Folks,

Currently i have Chart with Drill down group(Year/Month/Week), the additional requirement is when user drill downs to Week level(By clicking on month or a Bar) week displayed should be whole week.

e.g. Week 40 of a year half falls in Sept and half in October, when user clicks on October Week 40 should be showing data from days of Sept as well as October, i having difficulty in implementing this as once a month is selected how can we show data from days of other month.

Your help is appreciated as always.

Thanks,

ASP

[rubenmarin]

Hi Anwar, something like this:

=If(GetCurrentField(Group_name)='Week'

  Sum(TOTAL <Year, Week> expression), //The Sum can be any other function

  Sum(expression)

)

or

=If(Count(DISTINCT Week)=1

  Sum(TOTAL <Year, Week> expression), //The Sum can be any other function

  Sum(expression)

)

Not tested.

[jonathandienst]

If you override the selection of month in the expression and filter only by week, you should get what you want. Something like:

     Sum({<Week = {"$(=Max(Week))"}, Month>} Sales)

[jonathandienst]

If you want to make the expression depend on the drill down level, then use GetCurrentField to organise the expression like this:

     =If(GetCurrentField(DDGroup) = 'Week',

            Sum({<Week = {"$(=Max(Week))"}, Month>} Sales),

            Sum(Sales)

     )

[#5]

Try this,

=If(GetCurrentField(Group_name)='Week',  Sum({<Month=>} expression),   Sum(expression))

[#6]

Guys, thanks for your replies but i dont want the chart for just one week, if i have selected August it should be for all August month but 1st and last week of August should be complete which can fall under July and September.

[rubenmarin · Accepted solution]

Hi Anwar, try this:

=If(GetCurrentField(Group-name)='Week',

Sum({<Date={'>=$(=Date(WeekStart(Min(Date))))<=$(=Date(WeekEnd(Max(Date))))'}, Month>} Value),

Sum(Value)

)

[#8]

Hi Ruben,

I guess this will give me complete Year data if i ignore Month in Exp, some how i have to capture month find its min and max week and than play with it.

[rubenmarin]

Hi Anwar, you tried?

The 'true' part of the if only works when week is the dimension and week isn't ignored, so each week will show his data for the whole week ignoring the month.

[#10]

Thanks Ruben your suggestion worked, others thanks as well.

Just one thing in set analysis you have written "Month" without any = sign was that a typo?