10 Replies Latest reply on Dec 22, 2016 10:36 AM by Shinichiro Murakami

    Variances between standard and current month in a dynamic report

    Nicola Prime

      Hi All,

       

      Please help!! I am sure it supposed to be simple...but it is taking me ages to try and figure this out!

       

      I am trying to create a report whereby I show the cost per piece over the last 12 months - my report is dynamic so the user can change the current month and thus the preceding 11 months before.

       

      The problem I am encountering is that I am trying to show a variance column based on the "current month" and the Dec of the previous year.

       

      I have taken the following steps:

       

      1) I have created the formula where [Date Control] is my parameter to select the month.

       

           *Current Month Cost Per Unit*:                           IF ([Date]) = [Date Control] THEN [Cost Per Unit] ELSE 0 END

           *Last Month Last Year (LM LY) Cost Per Unit*:  IF ([Date]) = [LM LY] THEN [Cost Per Unit] ELSE 0 END

           * Variance Calc*:                                                 [LM LY Cost Per Unit] - [Current Month Cost Per Unit]

       

      2) I have added the variance calc to my rows as a discrete value - The problem here is that rather than each part sitting on one line, when I add this calculation, it sits on three lines - one line has the "current month" cost, one the Dec'15 cost and the other has the remaining months. I then get a variance for both the Dec'15 row and the current month row - summed up, the variance would be correct but the current way it is displayed is not helpful:

       

       

      (This is what it looked like before I added the variance calc):

      3) I thought I had a solution - to duplicate the report and create a formula firstly to only show the previous dec and the current month. This would then enable me to create the below formula:

             

                SUM([Cost Per Unit]) - LOOKUP(ZN(SUM([Cost Per Unit])), -1)

       

      However, although the calculation is then correct (i.e. it actually subtracts the values between Dec15 and Nov16, it still shows on two lines and shows a "Null":

      The report I am trying to produce is very long, with many part costs, and so the idea of a second worksheet may prove difficult when lining it up in the Dashboard...I have already noticed that the order doesn't always stay the same etc, and I also wouldn't know how to hide the Dec month in the second worksheet (as it will already be one of the months showing in the first worksheet).

       

      So my queries are as follows:

      1) How would you go about trying to present this long report with variance column for current month versus previous Dec (I will need to present the months in chronological order, so the previous Dec will never be in the same position compared to the current month)?

      2) How can I display just one row for each part so that comparison is easy and the variance calculates properly?

      2) How can I get the variance column to sit as the last column in the report?

      3) How can I apply a different colour only to the variance column?

       

      I'd be so grateful for any help you can offer, this is starting to give me a headache!

       

      Nicola

        • 1. Re: Variances between standard and current month in a dynamic report
          Shinichiro Murakami

          Hi Nicola,

           

          Thank you for describing the issue very in detail, but to be honest, I hesitate to read thru your question without seeing your data.

          That's really in vain, in case of without data.

           

          Please attach packaged workbook first.

          Packaged workbooks: when, why, how

           

          Thanks,

          Shin

          • 2. Re: Variances between standard and current month in a dynamic report
            Nicola Prime

            Hi Shin,

             

            Thanks for your response.

             

            I have tried to replicate what I am doing with the dummy Superstore data...but unfortunately there seems to be something odd with my variance formula...it is only working for some of the Product IDs - the rest it just returns zero. Perhaps you could show me how you might go about creating a variance column between the previous Dec and the current month and I will see if I can replicate it in my own data - as you say, this might be an easier way than answering my query above!

             

            Thanks a lot,

             

            Nicola

            • 3. Re: Variances between standard and current month in a dynamic report
              Shinichiro Murakami

              Nicala,

               

              To be honest, to meet all of you request is quite troublesome, in other words, very difficult for beginners.

              But anyways, I put some solutions here.

               

              Here are two versions, with variance different(Version 2) color and Not(version 1)

               

              version 1 is easier and less steps, but still you need time to digest it.

               

              [Month Filter]

              if attr(datetrunc('month',[Order Date]))=attr(datetrunc('month',[Control Month]))

              or

              attr(datetrunc('month',[Order Date]))=attr(dateadd('month',-1,datetrunc('year',[Control Month])))

              then "show" else "hide" end

               

               

              [Variance Current - Dec]  <==  as Discrete

              int(window_sum(ZN(sum(if datetrunc('month',[Order Date])=datetrunc('month',[Control Month])

              then [Sales] end)))

              -

              window_sum(ZN(sum(if datetrunc('month',[Order Date])=dateadd('month',-1,datetrunc('year',[Control Month]))

              then [Sales] end))))

               

               

               

              version 2 :  Detail concept is here, it's requires around 100 steps by itself

              Such a simple view, it required 100 steps!! - Having Multiple KPIs - - Still Struggling with Excel ?? <Tableau's Room>

               

               

               

              [Last December Sales]

              window_sum(ZN(sum(if datetrunc('month',[Order Date])=dateadd('month',-1,datetrunc('year',[Control Month]))

              then [Sales] end)))

               

              [This month Sales]

              int(window_sum(ZN(sum(if datetrunc('month',[Order Date])=datetrunc('month',[Control Month])

              then [Sales] end))))

               

               

              Thanks,

              Shin

              • 4. Re: Variances between standard and current month in a dynamic report
                Nicola Prime

                Hi Shin,

                 

                Thanks a lot for your reply. I will work through it today and see if I can make it work...may I ask though - if I want to display all the other months (previous 11 months + current month)...will it still work?

                 

                Thanks

                 

                Nicola

                • 5. Re: Variances between standard and current month in a dynamic report
                  Shinichiro Murakami

                  In version two, you need add column X 2 fields. I personally persuade better give up coloring variance. 

                  You might use dashboard instead.  Though, using dashboard has different constraints and need to see detail.

                   

                  The final goal is to balance between "workload and durability" and "interactive function and visibility".

                   

                  Thanks,

                  Shin

                  • 6. Re: Variances between standard and current month in a dynamic report
                    Nicola Prime

                    Hi Shin,

                     

                    Thanks for taking the time to explain all this.

                     

                    One of the issues with the standard version is that the Variance column still appears first - ideally I should like to see this last - after the current month. I think I can get the order to change for the colour one - so I will try to replicate this, I have worked with something previously you suggested using this approach so hopefully I can get my head around it.

                     

                    Can you help me understand though what the "INT" and "Window_Sum" are doing in your formula? The rest of the formula makes sense but I can't quite understand this part.

                     

                              int(window_sum(ZN(sum(if datetrunc('month',[Order Date])=datetrunc('month',[Control Month])

                              then [Sales] end)))

                              -

                             window_sum(ZN(sum(if datetrunc('month',[Order Date])=dateadd('month',-1,datetrunc('year',[Control Month]))

                             then [Sales] end))))

                     

                    I actually was playing around yesterday and came up with the attached (with only 3 months as an example), however, the problem here is that because I have to create a calculated field for all the 12 months and because it is dynamic...I can't name the columns with the month name - is there anyway I can change this so it picks up the correct month name? Without the month names, I can't share this with my end users.

                     

                    In fact...I think I will encounter the same issue with regards to the months names in the colour version?

                     

                    Thanks

                     

                    Nicola

                    • 7. Re: Variances between standard and current month in a dynamic report
                      Shinichiro Murakami

                      I'm not sure I remember correctly, but maybe ,window_sum is used to calculate total regardless of dimensions.  Standard sum's case, that calculation becomes null because of dimensions settings.

                      Int is changing decimal to whole number.  To use numbers as dimensions, decimal brings trouble.

                       

                      And you are right, bringing Month Name is another challenge which I don't have immediate answer right now.

                       

                      Thanks,

                      Shin

                      • 8. Re: Variances between standard and current month in a dynamic report
                        Shinichiro Murakami

                        There are "1" value difference , maybe 'round' is better than 'Int', BTW.

                         

                        Thanks,

                        Shin

                        • 9. Re: Variances between standard and current month in a dynamic report
                          Shinichiro Murakami

                          Give me one more try.

                          Kind of crazy approach but might be providing some hints for other case.

                           

                           

                          Using standard method

                           

                          [Variance Current - Dec (continuous)2]  <== Only shows variance on "Control Month"

                          if  attr(datetrunc('month',[Order Date]))= datetrunc('month',[Control Month]) then

                          int(window_sum(ZN(sum(if datetrunc('month',[Order Date])=datetrunc('month',[Control Month])

                          then [Sales] end)))

                          -

                          window_sum(ZN(sum(if datetrunc('month',[Order Date])=dateadd('month',-1,datetrunc('year',[Control Month]))

                          then [Sales] end))))

                          end

                           

                          [Variance Current - Dec (continuous)2 Negative]  <== Only shows values if negative with "|" text separator

                          if [Variance Current - Dec (continuous)2]<0 then "| "+str([Variance Current - Dec (continuous)2]) end

                           

                          [Variance Current - Dec (continuous)2 positive] <== Only shows values if positive with "|" text separator

                          if [Variance Current - Dec (continuous)2]>=0 then "| "+str([Variance Current - Dec (continuous)2]) end

                           

                           

                           

                          That's it.

                           

                          Thanks,

                          Shin

                          • 10. Re: Variances between standard and current month in a dynamic report
                            Shinichiro Murakami

                            And one more option to show the variance to the right end.

                            Only available when  the latest month > parameter control month

                             

                            [Header Date]

                            if DATEDIFF('month', [Order Date], [Control Month]) = -1

                            then "Variance" else datename('month', [Order Date]) end

                             

                            [Relative Date +1]

                            IF

                              DATEDIFF('month', [Order Date], #2014-12-01#) = 0 OR

                              DATEDIFF('month', [Order Date], [Control Month]) <11 and

                              DATEDIFF('month', [Order Date], [Control Month]) >= -1

                            THEN "Show" ELSE "Hide" END

                             

                            [Variance Current - Dec (continuous)2]

                            if attr([Header Date])="Variance" then

                            int(window_sum(ZN(sum(if datetrunc('month',[Order Date])=datetrunc('month',[Control Month])

                            then [Sales] end)))

                            -

                            window_sum(ZN(sum(if datetrunc('month',[Order Date])=dateadd('month',-1,datetrunc('year',[Control Month]))

                            then [Sales] end))))

                            end

                             

                             

                            [Sales SM]

                            if attr([Header Date])<>"Variance" then sum([Sales]) end

                             

                             

                            [Variance Current - Dec (continuous)2 Negative]

                            if attr([Header Date]) ="Variance" and [Variance Current - Dec (continuous)2]<0 then [Variance Current - Dec (continuous)2] end

                             

                            [Variance Current - Dec (continuous)2 positive]

                            if attr([Header Date])="Variance" and [Variance Current - Dec (continuous)2]>=0 then [Variance Current - Dec (continuous)2] end

                             

                             

                            Thanks,

                            Shin