7 Replies Latest reply: Nov 24, 2014 1:25 AM by Naga Balakrishnan RSS

    Full accumulation in chart

      Hi everyone,

          i have a problem with full accumulation option in charts with date in dimensions.

       

      When the chart show all dates the values are ok, but when i select just two o three months the chart show the accumulation of those months.

       

      Example:

       

      Month          Profit

      jan-2012      500$

      Dic-2011      400$

      Nov-2011      200$

       

      The full accumulation chart when you select nothing show:

      Nov-2011     200$

      Dic-2011     600$

      Jan-2012     1100$

       

      But, when i select the last two months this happend:

      Dic-2011     400$

      Jan-2012     900$

       

      And the real accumulate value to Jan-2012 is 1100$

       

      i saw this links whitout succes...

      http://community.qlik.com/message/120224

      http://community.qlik.com/qlikviews/1113

      http://community.qlik.com/qlikviews/1062

       

       

      any ideas?

      Thanks!

        • Re: Full accumulation in chart
          Karl Pover

          Review following post that uses a "asof" table or the attached file that uses a date island table:

           

          http://community.qlik.com/message/89017#89017

           

          Karl

            • Full accumulation in chart

              Hi Kart, thanks for the .qvw

               

              I couldn't understand how can i use this to resolve my issue....I need a chart with accumulative values and this values must be the same selecting two or three months like my example i explain before....

               

              Can you give me a hand with this?

               

              thanks!

                • Re: Full accumulation in chart
                  Karl Pover

                  Gerardo,

                   

                  Here's an example of an AsOf table.  You'll notice that if you select a later month in the graph the values will not change.

                   

                  Karl

                    • Full accumulation in chart

                      great! that i was looking for!

                       

                      now i will try to translate this to my model.....

                       

                      thanks!

                        • Re: Full accumulation in chart

                          Hi Karl,

                               i have problems with this in my model.

                           

                          I can't do nothing with this part of the sintax:

                           

                          AsOf:

                          Load distinct MonthYear

                          Resident Calendar;

                           

                          When i reload the .qvw the aplication can't found MonthYear dimension....and i don't know why.....

                           

                          this is the hole syntax

                           

                          SUBSCRIPTIONS:

                          LOAD

                              Year(modified) & Num(Month(modified),'00') & Num(Day(modified),'00') as %Key_MODIFIED_SUBSCRIPTIONS,

                              city_id,

                              city_id as %Key_CITY_SUBSCRIPTIONS_ID,

                              email,

                              is_voluntary,

                              is_subscribed,

                              modified,

                              user_id,

                              id as %Key_SUBSCRIPTION_ID

                          FROM [..\QVD\SUBSCRIPTIONS.QVD] (qvd);

                           

                          Calendar:

                          Load modified,

                              year(modified) as Year,

                              quartername(modified) as Quarter,

                              monthname(modified) as MonthYear,

                              month(modified) as Month,

                              week(modified) as Week,

                              day(modified) as Day

                          FROM [..\QVD\SUBSCRIPTIONS.QVD] (qvd);

                           

                          AsOf:

                          Load distinct MonthYear

                          Resident Calendar;

                           

                          Left Join (AsOf)

                          Load MonthYear as MonthYearAsOf

                          Resident AsOf;

                           

                          Inner Join (AsOf)

                          Load *

                          Resident AsOf

                          Where MonthYearAsOf >= MonthYear;

                           

                          thanks for any advice.....

                          gerardo

                            • Re: Full accumulation in chart
                              Karl Pover

                              Gerardo,

                               

                              If that's the whole script then I don't see any problem with it.  I would load the script up until the Calendar table and verify in the interface that the Calendar table is created and has values.  QlikView automatically concatenates tables that have the same columns and that sometimes causes errors of that table not being found when you call that table later in the script with the resident command.  You can prevent that error by using the Noconcatenate function before the load statement.

                               

                              Table:

                              Noconcatenate
                              Load *

                              From ...;

                               

                              Tell us what you find.

                               

                              Karl

                        • Re: Full accumulation in chart
                          Naga Balakrishnan

                          Hi Karl, Thank you so much for the solution

                          -Nisha