Help with Chart - Display first day of week data only.

[chris1987]

Hi,

I have a table that contains information that I want to display in a graph. A sample of the table is here:

(There are other Categories and Types – this are just the first records In the table).

Date	Day	Week	Month	Year	Category	Type	Value
26/06/2015	6	26	6	2015	MTO Not Made	Due	50000
29/06/2015	2	27	6	2015	MTO Not Made	Due	200000
30/06/2015	3	27	6	2015	MTO Not Made	Due	175000
01/07/2015	4	27	7	2015	MTO Not Made	Due	100000
02/07/2015	5	27	7	2015	MTO Not Made	Due	50000
03/07/2015	6	27	7	2015	MTO Not Made	Due	50000
06/07/2015	2	28	7	2015	MTO Not Made	Due	125000
07/07/2015	3	28	7	2015	MTO Not Made	Due	60000
08/07/2015	4	28	7	2015	MTO Not Made	Due	150000

I have setup a chart which tracks the daily movement of each Type and this works perfectly. In the expression I have “=sum([Value])”. With the “Date” along the axis and then each "Type" in a stacked graph.

What I want to do is have another graph which will show the starting position of each week – so just show the value for Monday of each week. What I was thinking is from available fields in the above information, display each value if the “Day” = 2 (for Monday) but I’m having trouble putting this in an expression. I will have “Year” and “Week” along the axis and then just the "Day 2" number in the value area.

Any help would be appreciated.

Cheers

Chris

[sunny_talwar · Accepted solution]

May be this:

=Sum({<Day = {2}>}Value)

[chris1987]

Perfect!

That's also prompted me to ask the next question....

How would I get the info for the first day of each month? I've also added a "MonthDay" column to the table, This simple list what day of the month the date it e.g. 09/02/16 --> 9th day of the month (I'm in UK date format dd/mm/yyyy).

I'll only have weekdays in the table, therefore I can't have a fixed integer like "{2}" I was thinking along the lines of using the min() function some how?

Thank again

Chris

[sunny_talwar]

There is a function called MonthStart() which would give you the start of each month. So in your script you can create a flag like this may be:

LOAD Date,

          If(Date = Floor(MonthStart(Date)), 1, 0) as Flag

and then use this flag on the front end:

Sum({<Flag = {1}>} Sales)

[chris1987]

Thanks for the reply, but I don't this will work as sometimes the month start will fall on a weekend and I'll not have these dates in the table, only weekdays. MonthStart() will always return 01/02/2016 for example, even if the 1st is on a weekend? I need to somehow look a my table and do something like =sum(Date={"=min(MonthDay)"}Value) but that doesn't work

[sunny_talwar]

I suggest creating a mastercalendar and then you won't have the problem. The issue with =sum(Date={"=min(MonthDay)"}Value) is that the set analysis is evaluated once per chart. So based on different dimension you might not see the result you expect. Alternative is to use if statement, but can slow down performance.