12 Replies Latest reply: Apr 16, 2014 3:30 AM by Stefan Fischer RSS

    Sum values and counting rows with a macro

    Stefan Fischer

      Hi,

       

      I have a problem with the addition of values in a table. I guess it can be solved with a macro. Unfortunately I have no experience in with makros. It should calculate the first 12 values in an additional column and the number of rows should be counted. The rows with null values are not be counted.

       

       

      Regards

      Stefan

        • Re: Sum values and counting rows with a macro
          Dariusz Mielczarek

          Stefan,

           

           

          I'm pretty sure that you do not need a macro.

          I think, you need to prepare appriopriate data model and then use normal expressions with some conditions n your charts.

          If you want some example, please explain in more detail what source data do you have and provide sketch of analytics you would like to prepare in QV.



          regards

          Darek

            • Re: Sum values and counting rows with a macro
              Stefan Fischer

              Hi Darek,

               

              thanks for your reply. There is a tricky problem. I've created a special formula in cal 52 week to calculated values. But if is a 0 value in first 12 row, i've get a wrong result.

               

              Regards

              Stefan

                • Re: Sum values and counting rows with a macro
                  Dariusz Mielczarek

                  Stefan,

                   

                  please share sample with data model.

                   

                  regards

                  Darek

                      • Re: Sum values and counting rows with a macro
                        Dariusz Mielczarek

                        Stefan,

                         

                        i'am sorry, because maybe i'am not clear about your business needs. Anyway, when i see expression like this:

                         

                        = If( RowNo(TOTAL)=1, rangeavg (below(total sum(Value),0,12)),

                              If( RowNo(TOTAL)<=2,

                                ((below(total sum(Value),-1,1)

                                + below(total sum(Value),0,1)

                                + below(total sum(Value),1,1)                                                                                                                                                                                    

                                + below(total sum(Value),2,1)

                                + below(total sum(Value),3,1)

                                + below(total sum(Value),4,1)

                                + below(total sum(Value),5,1)

                                + below(total sum(Value),6,1)

                                + below(total sum(Value),7,1)

                                + below(total sum(Value),8,1)

                                + below(total sum(Value),9,1)

                                + below(total sum(Value),10,1))/12),

                                (((above(total sum(Value),0) * vB0 + above(total sum(Value),1) * vB1 + above(total sum(Value),2) * vB2) -

                                 (below(total Column(2),-1,1) * vA1) - (below(total Column(2),-2,1) * vA2)) / vA0)))

                         

                        my first thought is that may be something wrong

                        I suggest you to start from the beggining. Let me know what is your source data and what result you need in application. You may need to adjust somehow the data model

                         

                        regards

                         

                        Darek

                          • Re: Sum values and counting rows with a macro
                            Stefan Fischer

                            Hi,

                            this calculation will be planning line chart. Pls look at pic.

                            First and second value is the average of 12 value in row SU. At row three last formula will be calculated.

                             

                            Regards

                            Stefan

                              • Re: Sum values and counting rows with a macro
                                Dariusz Mielczarek

                                Stefan,

                                 

                                your charts looks nice

                                I can see, that in your xls column 52 woche is calculated from some fields from columns B adn C and also some basel values.

                                I can see also, that in your QlikView sample those 52 weeks and 12 week values you load inline, so, i can expect, that you plan load them "from outside" or calculate it in your script. Anyway i feel, that you plan, that those fields will be calculated and ready to show just after data reload.

                                I dont  know your business case, but don't you need any selections and calculations "on the fly" in your application?

                                 

                                About your "avg" problem - where is it? In load script or in the chart expression?

                                 

                                And last question.... where are you from? Maybe we can meet and take a look closer on your case ?

                                 

                                regards

                                Darek

                                  • Re: Sum values and counting rows with a macro
                                    Stefan Fischer

                                    Hi Darek,

                                     

                                    this function will damp the line in my chart. My problem is, if values have 0 or I get less as 12 rows, this formula will not work correct. I will load data from a SQL-Database.

                                    I’m from Germany.

                                     

                                    Regards
                                    Stefan

                                      • Re: Sum values and counting rows with a macro
                                        Dariusz Mielczarek

                                        Stefan,

                                         

                                        i understand, that Value column (in your QlikView table SU column) is data in week granularity. You getting it from external database.

                                        1. I try to understand if 52 week and 12 week KPI's you need:

                                        - get from externel database, 

                                        - calculate in load script

                                        - or maybe calculate it on the fly (using chart expression).

                                         

                                        I understand, that for each week you have some KPI (12 weeks) related to last (or maybe next) 12 weeks.

                                        2. Is 52 weeks KPI also related to 52 last (or maybe next) weeks?

                                        3. Do you need in your appliocation some another KPI's, like for example 24,40, etc. weeks, or maybe you like to let user set number of weeks to have dynamic X weeks KPI?

                                         

                                        I'am not sure if it is difference you are talking about, but i can see in your table that for instance for week 2011/34 you have value 14331 in column "52 weeks" and value 13.829 in column "cal 52 weeks".

                                         

                                         

                                        I'am in Warsaw, so i have 6 hours by car to Berlin

                                         

                                        regards

                                        Darek