Show unique period in graph

[Bart_Breekveldt]

Dear community,

I've set up a custom time period (4 weeks). Our company works with periods instead of months. My script is as follows:

=Dual('P'&Num((Week([Order.Leverdatum])+1)/4,00),Num((Week([Order.Leverdatum])+1)/4,00))

(Order.leverdatum (Dutch) translates to order.delivery_date)

However in a graph it looks like this (see below). When selecting "period" I get the same graph as when I select week. The period shows up four times and are aggregated as weeks.

I've tried GROUP BY, AGGR and DISTINCT funtions, but it didn't help. What am I doing wrong?

How can I aggregate these period, so my graph show the orders per period?

Thanks in advance

[JordyWegman · Accepted solution]

Hi Bart,

The thing that you need to do is to create a calendar for your data model. In this calendar you can have dates, weeks and periods. Then you only have to select periods as your dimension and you don't have to aggregate (graph) or group by (script).

Jordy

Climber

[NZFei · Accepted solution]

1) The first part of dual function is the text format. The second part is the number value.

2) Num function only change the format of the expression, it does not change the values behind the scene. So it looks number 01 but behind the scene the values are 0.5, 0.75, 1, 1.25. When you select P01 as period in the front, behind the scene you have selected four values:

P01, 0.5

P01, 0.75

P01, 1

P01, 1.25

That is why P01 will show four times, ideally they are four values with the same outlook format.

To fix this, just use the round function first instead of just num function:

=Dual('P'&num(Round((Week([Order.Leverdatum])+1)/4),'00'),Round((Week([Order.Leverdatum])+1)/4))

[Bart_Breekveldt]

Hi @JordyWegman @NZFei thanks for your help! I solved the issue with the AutoCalendar @JordyWegman proposed where I used the formula which you came up with @NZFei . Great cooperation 🙂 For everyone that use periods: use WeekYear if you calculate periods on basis of weeks. Week 1 (it sets like 29th of december until 4th of january as week 1).