2 Replies Latest reply on Jun 13, 2016 2:13 AM by Kévin Robert

# [WHY] Fixed, Case When, Time Granularity and....*** Result

Hello,

I'm encounting a very strange behaviour. I'm calculating in the same view a measure (named : "Taux de collecte") for a combinaison of filter and detail (regionnal) and also the same calculation at the National level.

This is my calculation that work correctly

{FIXED [Palier C], [Phase C], [Date de référence] : [Taux de collecte]}

Notice that [Taux de collecte] = SUM([Nb. de C collectés à 8h]) / SUM([Nb. de C éligibles à la collecte])

So for each day and by "Palier C" and "Phase C" i'm calculating the measure "taux de collecte" This measure can not be > 100%

Where it's become a little bit strange it's when i decided to transform my static view by day into a dynamic view set by a parameter which allow to select the date level (day, week or month)

this is my calculation :

CASE [Param_Date_Select]

WHEN 'Jour' THEN {FIXED [Palier C], [Phase C], [Date de référence] : [Taux de collecte Select]}

WHEN 'Semaine' THEN {FIXED [Palier C], [Phase C], [Semaine de date] : [Taux de collecte Select]}

WHEN  'Mois' THEN {FIXED [Palier C], [Phase C], [Mois de date] : [Taux de collecte Select]}

END

Where

[Semaine de Date] = DATETRUNC('week',[Date de référence])

[Mois de Date] = DATETRUNC('month',[Date de référence])

My Axis is not [Date de référence] anymore but :

CASE [Param_Date_Select]

WHEN "Jour" THEN DATETRUNC('day', [Date de référence])

WHEN "Semaine" THEN DATETRUNC('week', [Date de référence])

WHEN "Mois" THEN DATETRUNC('month',[Date de référence])

END

With this calculation, my "Day" level is still working well. But my Week And Month Level Sum sometimes the measure :

By Week
By Month

At the week level only the last week is correct. And at the Month level only the first is correct.

But where it's become completly wired is when i suppress the line of the day and month in my Case When calculation : it's working for weeks !!!

Calculation

CASE [Param_Date_Select]

WHEN 'Semaine' THEN {FIXED [Palier C], [Phase C], [Semaine de date] : [Taux de collecte Select]}

END

Result :

So ! My Calculations are correct but when i'm using them in a Case When....it does not work anymore for Week and Month. But if it's the only calculation in the case when...It's work. And in all case, my day level work...

I tried to duplicate my based measure to forced the calculation on different column...no result.

HELP !!!!

• ###### 1. Re: [WHY] Fixed, Case When, Time Granularity and....*** Result

Hi Kevin,

From your weekly / monthly pictures

it looks like you have your [Date de référence] field

somewhere on a view at the granularity of Day.

And you're using your FIXED LOD as SUM() aggregation.

Since your FIXED LOD is a Row-Level calculation,

and it is in essence the Ratio one (like Profit/Sales),

aggregation after calculation gives a wrong result

most of the time.

I suggest you to use MIN() aggregation for your FIXED LOD by default.

Or better to change FIXED LOD to an INCLUDE one if appropriate.

Yours,

Yuri

1 of 1 people found this helpful
• ###### 2. Re: [WHY] Fixed, Case When, Time Granularity and....*** Result

Hello Yuriy !

The default min work well ! Thanks a lot.

I cannot use an Include in cause of many falter that a want to be execute after the calculation.

Thanks again

Kévin