7 Replies Latest reply: Oct 14, 2011 12:39 PM by jenvontaylor RSS

    Waterfall diagram

    hurabaia

      Hi Guys,

       

      I want to make a waterfall graf. The first bar should be the target costs. The second bar is the cost category1 target-actual, the third is the cost category2 target-act, etc.

      I know from the forum how to use offset function. The problem is the offset of the second bar should be the total planned cost. I can't calculate this in the chart, because the dimension is the cost category, and the sum of planned costs is "fileted" because of this.

      Can somebody help me, please?

        • Waterfall diagram

          Can you use Sum(Total PlannedCost)? That should give you the total, not matter what the filtering/dimension is.

           

          Regards,

            • Waterfall diagram
              hurabaia

              Thank you Miguel, I was thinking about Total, but I couldn't find in the hepl, so i thought there is no such a function.

              My next challange is to calculate the offset for the third bar. It is like Planned cost + category1 tg-act. I am thinking about if there is a function which returns the last bar's value.

               

              Thank you very much.

               

               

                • Waterfall diagram
                  Karl Pover

                  How many cost categories are there? If there are few categories and they don't change very often you might want to think about not defining any dimension and only expressions that you filter with a sum(if) or better yet set analysis.

                  Regards.

                    • Waterfall diagram
                      hurabaia

                      I used your suggestion sum(if) stuff. It is working fine. Yes

                        • Waterfall diagram
                          hurabaia

                          I forgot to say thank you.

                            • Waterfall diagram

                              Just to add a little to this, I make quite a lot of use of waterfall charts and what I use for the offsets is generally the column references. QlikView allows you to directly reference the value of a column, so what you might have is these columns:

                              Sum(Sales) - with a label called Sales

                              Sum(Cost) - with a label called Cost

                              Sum(XYZ) - with a label called XYZ

                              Now, the first column starts at zero and therefore has no offset, the second column starts at the point where the first column ends, so in this case I could use the same expression Sum(Sales) OR I could use Column(1) OR I could use the label [Sales].

                              The third column should start at Sales - Cost, and again I could do this with the expressions, but I could also do it by using Column(1)-Column(2) OR I could use [Sales]-[Cost]

                              I find this very useful because it means you can change your expressions in the columns and not worry about the offsets, you just have to be aware of things if you change the order of the expressions or the label names.

                               

                              Hope it's helpful,