Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ];
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:
- Marcus
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!!
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