How to set a dimension to consider weeks when I choose a especific date?

[#1]

Hi,

I have a bar graph with a dimension called "Week". This field tells the corresponding week of a date of the year, like shown in the table below.

My problem is that I want the graph to show all 4 weeks (there's always four weeks per month) of the particular month that I choose. But for other analisys purposes, the user also have to choose a exact day, and when he does that, the graph only shows the especific week of that date.

I want the graph to consider the year and the month chosen to give me always the four weeks of that month, but I want the dimenson to disconsider the "day", so it won´t give me just the one week of that especific date.

Thanks for helping.

Week	Data
JAN 01	1/1/13
JAN 01	2/1/13
JAN 01	3/1/13
JAN 01	4/1/13
JAN 01	5/1/13
JAN 01	6/1/13
JAN 01	7/1/13
JAN 02	8/1/13
JAN 02	9/1/13
JAN 02	10/1/13
JAN 02	11/1/13
JAN 03	12/1/13
JAN 03	13/1/13
JAN 03	14/1/13
etc	...

[cesaraccardi · Accepted solution]

Hi,

I think you can use some set analysis on the chart expressions to ignore the selections of the "Data" field, for instance:

Sum({$<Data=>} Value)

You shall see the expected result if you have the Week as a Dimension.

Regards,

Cesar

[#3]

Thanks Cesar,

You are absolutely right. But now I'm having problems to atribbute this one of my expressions.

First expression was =sum([Actual]), then I changed to =sum({$<Day=>}[Actual]) and it worked.

The second is

= Round( (sum (Goal) / Ceil(Num(MonthEnd(Max(Date)) - MonthStart(Min(Date))),1,0) ) ,0.001   *   (Num(Aggr(Max(Date), Week) - Aggr(Min(Date), Week))+1)

I divide my mothly goal by the days of that month, then I multiply it for the days of the week. This is to create a weekly goal.

I've tried the code below, but it didn't worked, could you help me?

= sum({$<Day=>}

Round( (sum (Goal) / Ceil(Num(MonthEnd(Max(Date)) - MonthStart(Min(Date))),1,0) ) ,0.001   *   (Num(Aggr(Max(Date), Week) - Aggr(Min(Date), Week))+1)

)

[cesaraccardi]

Hi,

Assuming that you already have data at the "Date" level, would it be possible to replace this expression by a simple sum of the calculated goal for each day? I mean, transfer this logic to the script and pre-calculate the values for each day?

Regards,

Cesar