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

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

    Kévin Robert



      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]}





      [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])



      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 !!!




      CASE [Param_Date_Select]

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



      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 !!!!