5 Replies Latest reply: Apr 21, 2010 12:58 PM by John Witherspoon RSS

    How I get the simple values out of an aggregation?

      Hello together,

      I am a Collage Student and really unexperienced in QlikView. But still I hope someone helps me out a little bit.

       

      Here ist my Problem.

      I want to make a simple bar diagramm with the data (Sold shoes):

       

      jan 15

      feb 20

      Mar 10

       

      But my Problem is, that I only have the aggregated values (Sold shoes)

       

      jan 15

      feb 35

      Mar 45

       

      the normal form would be: sum (total shoes)

      but now i need is something like:

      sum(total shoes) -sum(month-1 total shoes)

       

      I hope you understand my problem.....sorry, my english is kind of limited.

       

      I would be very glad if someone could help me out!

      Greetings

      -Nils-

       

       

       

       

        • How I get the simple values out of an aggregation?
          John Witherspoon

          Can you convert back to simple data during the load? You can always aggregate again in a chart later if you need to.

          LOAD
          Month
          ,rangesum(AggregatedShoes,-previous(AggregatedShoes)) as Shoes
          INLINE [
          Month, AggregatedShoes
          jan, 15
          feb, 35
          Mar, 45
          ];

          • How I get the simple values out of an aggregation?

            Hi Nils,

            If i understood your question properly then below is the suggestion,

            if you want to accumulate the sale with you previous month sales; In the expression tab, select your expression (Sum(Sales)) and check the box "Full Accumulation" beneath the expression.

            Regards

              • How I get the simple values out of an aggregation?

                @ Rajesh Jeyaraman

                no, i exactly need the opposite.

                i have the accumulated data and want a chart without the accumulation

                • How I get the simple values out of an aggregation?

                  at John Witherspoon

                  LOAD
                  Month
                  ,rangesum(AggregatedShoes,-previous(AggregatedShoes)) as Shoes

                   

                  that works fine!

                  I dont unserstand the inline expression but that code above works fine.

                  But now I Have the problem that i got differnt salesmen.

                  My Data looks like

                   

                  Month Shoes Salesman

                  Jan 10 Name1

                  Feb 15 Name1

                  Mar 15 Name1

                  Jan 5 Name2

                  Feb 30 Name 2

                  Mar 35 Name 2

                   

                  How do I get the load expression saparated by salesmen?

                   

                   

                   

                   

                   


                   

                   


                   


                    • How I get the simple values out of an aggregation?
                      John Witherspoon

                      AggregatedShoes - if(Salesman=previous(Salesman),previous(AggregatedShoes),0)

                      The INLINE statement was just a way of loading some hardcoded data. It allows us to test that something works before hooking it up to the real data source. In this case, I don't have access to your data source, so it was a practical way of reproducing your data. It's also something that anyone could cut and paste to see that it works. Previous() looks at the row before this one in the source table. Rangesum(A,B) is just like doing A+B, except that if either is null, it treats them as 0, so that you still get the other value. That was necessary since the previous(AggregatedShoes) row on row 1 would return null. We don't need the rangesum() above because we're handling nulls differently. Here, if we're on row 1, the previous(Salesman) will be null, which is not the same as the current row's Salesman, so we return 0 explicitly. We do the same when the Salesman changes. But as long as we're within the same Salesman, each row subtracts the previous aggregation.