8 Replies Latest reply: Aug 24, 2012 9:38 AM by Marcos Herrera RSS

    RangeAvg - Above problem

      Hi Dear QV Community

       

      I have a problem with an expression that has the following formula in a line graph from a Bar/Line  Graph Object

       

      (

      IF (Año=Year(Today()) AND Mes=MONTH(Today())

      ,RangeAvg(Above(sum({<Base={1},$(vIgnoraProducto),Año={$(=Year(Today()))},Mes={$(=MONTH(Today()))},Día={$(=if(getselectedcount(Día)>0,Día,DAY(today())))}>} [Valor total cartera]),0,3))

      ,RangeAvg(Above(sum({<Base={1},$(vIgnoraProducto)>}[Valor total cartera]),0,3))

      /

      RangeSum(Above(sum({<Base = {1}>} [Valor ventas]),0,3))

      )

      )*90

       

      BarLine_Graph.png

       

      If you check the graph the information is up to Aug 2012 for the bar graph, but the line graph corresponding to the formula above comes up to Oct 2012

        • Re: RangeAvg - Above problem

          This is displaying correctly. The Rangeavg is taking 2 rows before the current and the current row. for sep you are getting the totals of Jul, Ago, and Sep.

          Oct is giving you Ago,Sep, and Oct

            • Re: RangeAvg - Above problem

              Ok marcsliving i know this but i need some solution over this issue

                • Re: RangeAvg - Above problem

                  How are your date fields linked in your data model? I am assuming that you can have months with no data linked correct?

                   

                  One way could be to use an if statment to return null if the month is greater than month(today()) so something like this. You would then be able to check suppress nulls under dimension:

                   

                  IF(MONTH(Yourdatefield)<=MONTH(TODAY()),

                  (

                  IF (Año=Year(Today()) AND Mes=MONTH(Today())

                  ,RangeAvg(Above(sum({<Base={1},$(vIgnoraProducto),Año={$(=Year(Today()))},Mes={$(=MONTH(Today()))},Día={$(=if(getselectedcount(Día)>0,Día,DAY(today())))}>} [Valor total cartera]),0,3))

                  ,RangeAvg(Above(sum({<Base={1},$(vIgnoraProducto)>}[Valor total cartera]),0,3))

                  /

                  RangeSum(Above(sum({<Base = {1}>} [Valor ventas]),0,3))

                  )

                  )*90)

                    • Re: RangeAvg - Above problem

                      I used the formula

                       

                      IF(MONTH(Yourdatefield)<=MONTH(TODAY()),

                      (

                      IF (Año=Year(Today()) AND Mes=MONTH(Today())

                      ,RangeAvg(Above(sum({<Base={1},$(vIgnoraProducto),Año={$(=Year(Today()))},Mes={$(=MONTH(Today()))},Día={$(=if(getselectedcount(Día)>0,Día,DAY(today())))}>} [Valor total cartera]),0,3))

                      ,RangeAvg(Above(sum({<Base={1},$(vIgnoraProducto)>}[Valor total cartera]),0,3))

                      /

                      RangeSum(Above(sum({<Base = {1}>} [Valor ventas]),0,3))

                      )

                      )*90)

                       

                      and the QlikView is blocked (Not Responding).

                       

                      And i use the calendar in FlagMatrix Method for this reason i use the Flag Base=1 that is a flag for selectons in time

                        • Re: RangeAvg - Above problem

                          Try doing this on the script level.

                           

                          IF((Yourdatefield)<=(TODAY()), Month(Yourdatefield)) as Month

                           

                           

                          This should make it so it will not pull any date greater than today for this month field.

                            • Re: RangeAvg - Above problem

                              This option is not viable because I have records with dates greater than today

                                • Re: RangeAvg - Above problem

                                  Ok I guess I will try to understand your original formula a bit more.

                                   

                                  From what I understand is that if the chart is on the current month and current year, use this formula:

                                  RangeAvg(Above(sum({<Base={1},$(vIgnoraProducto),Año={$(=Year(Today()))},Mes={$(=MONTH(Today()))},Día={$(=if(getselectedcount(Día)>0,Día,DAY(today())))}>} [Valor total cartera]),0,3))

                                   

                                   

                                  otherwise use this formula:

                                  RangeAvg(Above(sum({<Base={1},$(vIgnoraProducto)>}[Valor total cartera]),0,3))

                                  /

                                  RangeSum(Above(sum({<Base = {1}>} [Valor ventas]),0,3))

                                   

                                  What happens if you add one more part to that first IF statement?

                                   

                                  (

                                  IF (Año=Year(Today()) AND Mes=MONTH(Today())

                                  ,RangeAvg(Above(sum({<Base={1},$(vIgnoraProducto),Año={$(=Year(Today()))},Mes={$(=MONTH(Today()))},Día={$(=if(getselectedcount(Día)>0,Día,DAY(today())))}>} [Valor total cartera]),0,3))

                                  ,IF(Año=Year(Today()) AND Mes<MONTH(Today())

                                  ,RangeAvg(Above(sum({<Base={1},$(vIgnoraProducto)>}[Valor total cartera]),0,3))

                                  /

                                  RangeSum(Above(sum({<Base = {1}>} [Valor ventas]),0,3))

                                  )

                                  )

                                  )*90