2 Replies Latest reply on Oct 6, 2016 9:54 AM by Elina Kokkonen

    Repeat maximum date value for level of detail if no data every month

    Elina Kokkonen

      Hi,

       

       

      I’m having a problem that I hope can be figured out easily . See the attached file I have tried to explain my problem. I have different level of information and already cumulative amount calculated in database.

      I need to have level of detail calculation where I calculate maximum over bank, chanel, agreement and month and then sum those up.

       

      SUM({include [Bank], [Chanel],[Agreement],[Month year]:max([cumulative])})

       

      Problem is that not every bank or bank & agreement have the same row amount every month, so for example Bank A might be missing from September data totally even it had data in August. In my cumulative graph it just shows it goes down!

      So what I’m missing is that how to find max (cumulative) for maximum date for bank, chanel, agreement and repeat that number for month where no data? Like max({include [Bank], [Chanel],[Agreement]:

      max([Day])})

       

      but Tableau won’t let me use this inside LOD made already like this: SUM({include [Bank], [Chanel],[Agreement], max({include [Bank], [Chanel],[Agreement],

      max([Day])}):

      max([cumulative])}))

       

      I have tried different kind of lookups but still not working.

       

      Can anyone help me with this?

       

      Thank you!

       

      Br, elina

       

        • 1. Re: Repeat maximum date value for level of detail if no data every month
          Shinichiro Murakami

          Elina,

           

          Theoretically, this formula could show the value in the chart.

          However, I'm afraid that you will miss "real" 0 from the data.

          I mean that this formula brings last month value in all the case where original was "0"

           

          [ZN Lookup Max]

          (if ZN(lookup(SUM({include [Bank], [Chanel],[Agreement],[MOnth year]:max([cumulative])}),0))=0

          then lookup(SUM({include [Bank], [Chanel],[Agreement],[MOnth year]:max([cumulative])}),-1)

          else SUM({include [Bank], [Chanel],[Agreement],[MOnth year]:max([cumulative])}) end)

           

          Thanks,

          Shin

           

          • 2. Re: Repeat maximum date value for level of detail if no data every month
            Elina Kokkonen

            Thank you Shin for taking time for answering me! We are getting closer but if I remove bank from the rows now I get the same result as before.  I need to show anyhow totals for monthly (and actually daily or weekly depending what user selects)

             

            With this calculation I'm now getting correct answer with this data but it still does not go right with my real data .. getting closer numbers but not yet quite so I think this max monthyear does not work properly yet

             

            if  max([MOnth year]) <> max([max monthyear]) then

            ZN(LOOKUP(([Sum of max cumulative]),0)) else ZN(LOOKUP(([Sum of max cumulative]),-1)) END

             

            where max monthyear = {max monthyear}

            lookup calc.png

            Br, Elina