Sequential number instead of Month Day

[luisccmm]

Hi,

I want to compare in a Line graph sales from September and November by Days.

The problem is that Month_days (1-30) from September 2020 and October 2020 do not match with the same Weekdays (monday- Sunday).

I was think to replace Month_days (1-30) for just a sequential number so I can overlap both Lines in the same graph and in fact Number 1 matches with  same Weekday from both months. (Think that sales on weekends are lower from Monday-to-friday that is why I need to do that).

I look to compare 15 days range from September and October with a LINE GRAPH with 2 dimensions:

DIMENSION 1:Day function transformed like= aggr(rank(Day),Day)

DIMENSION2:Month

MEASURE: SUM( {<Date={">=8/09/2020<=23/09/2020",">=13/10/2020<=28/10/2020"}>} SALES)

Note: Date Format:  'DD/MM/YYYY'

Those adjustments (above) are producing the following.

ROW DATA:

Row data on the graph without applying AGGR function looks like the following

DIMENSION 1:Day

DIMENSION2:Month

MEASURE: SUM( {<Date={">=8/09/2020<=23/09/2020",">=13/10/2020<=28/10/2020"}>} SALES)

What I am looking for is instead of Real Days have a sequential number from 1 till 15 and match the beginning of each Month range with number 1 and so on (Date={">=8/09/2020<=23/09/2020",">=13/10/2020<=28/10/2020"}).

Thanks in advance for your help.

Table:

LOAD * Inline [

Day, Month ,Sales

8 , ‘Sept.’ , 209

9 , ‘Sept.’ , 212

10 , ‘Sept.’ , 199

11 , ‘Sept.’ , 139

12 , ‘Sept.’ , 77

13 , ‘Sept.’ , 39

14 , ‘Sept.’ , 240

15 , ‘Sept.’ , 233

16 , ‘Sept.’ , 258

17 , ‘Sept.’ , 214

18 , ‘Sept.’ , 181

19 , ‘Sept.’ , 72

20 , ‘Sept.’ , 51

21 , ‘Sept.’ , 263

22 , ‘Sept.’ , 189

23 , ‘Sept.’ , 178

13 , ‘Oct.’ , 241

14 , ‘Oct.’ , 222

15 , ‘Oct.’ , 214

16 , ‘Oct.’ , 191

17 , ‘Oct.’ , 101

18 , ‘Oct.’ , 35

19 , ‘Oct.’ , 226

20 , ‘Oct.’ , 211

21 , ‘Oct.’ , 210

22 , ‘Oct.’ , 220

23 , ‘Oct.’ , 174

24 , ‘Oct.’ , 59

25 , ‘Oct.’ , 51

26 , ‘Oct.’ , 251

27 , ‘Oct.’ , 224

28 , ‘Oct.’ , 223 ];

[marcus_sommer]

I think that what do you want to do isn't possible. At least not with a classical calendar because the different number of days within a month, the overlap of weeks between the months and the moving weekday will always lead to some kind of offset respectively blurring within your views.

Further I'm not sure if there is really an added value from such a view. Personally I would tend to use various views - one of day-level, one on a workday-level, one on weeks, another on the weekdays ... None of them will give a complete picture but all of them combined interpreted should provide a quite clear picture of the data.

An alternatively to the above may be to implement another type of calendar, like:

4-5-4-Master-Calendar 

- Marcus

[luisccmm]

Hi @marcus_sommer  thanks for your quick response.

From you suggestions seems you are thinking of script solution, but it´s not also possible to do something just for a simple pair of day ranges from 2 months, not even with Aggr function?? As I suggested?

Thanks a lot!!

[marcus_sommer]

I don't want to say that's not possible but it's probably not so easy respectively it may have the one or other disadvantages. The main challenge would be to synchronize both sets of dimension-values. If you really want to go this way I could imagine that creating a synthetic dimension with valuelist() - it's quite seldom that I need such dimension and if I wouldn't use valuelist() else creating such list within the script with an inline-table as loosen table - may be better as any aggr() construct (although the expression-logic would be more expensive).

Like already hinted I wouldn't do it in this way else using the mentioned dimensions from a master-calendar (I assume they did already exists) because why should I try to solve komplex matters within the UI if I could do most things much easier within the script.

- Marcus