9 Replies Latest reply: Jun 2, 2017 3:20 AM by Onno van Knotsenburg RSS

    Cum count error

    Jonathan Ruiz

      I have this situation, I have a qvd in which each month has the accumulated records of the previous month, so I would only have to count them, for example for 2016 the value to show is from Dec 2016, for Q1 is from March 2016 , Q2 is June 2016, this I should see it in a pivot table but I could not do it, and tried to use the formula Count({Periodo = {'$(=MonthName(Max(Periodo))'}Recurso) but this is only me Shows the data of the last period of all data, and when using

      Count({<Periodo={'=Monthname(Max(Periodo))'}>}Periodo) shows the calculation for the whole year and not a particular period, my goal is to show for example for the year 2016 the count of December 2016, for Q2 of 2016 the count of June 2016, thanks to who can help me

        • Re: Cum count error
          Vasiliy Beshentsev

          Hello, again.

           

          Maybe it's will be ok for you to use pivot table like 2017-06-01 12_36_49-Qlik Sense Desktop.pngthis?

            • Re: Cum count error
              Jonathan Ruiz

              Hi Vasily

               

              I need to achieve this result

               

              Año.png

              Quarter.png

               

              Mes.png

                • Re: Cum count error
                  Vasiliy Beshentsev

                  Log:

                  LOAD

                      AutoNumber(Id_Recurso) as Id_Recurso,

                      Log_Periodo as Periodo,

                      Year(Log_Periodo) as Año,

                      Month(Log_Periodo) as Mes,

                      Ceil(Num(Month(Log_Periodo))/3) as Quarter

                  FROM [lib://Acumulado/Log_Acumulado.qvd]

                  (qvd);

                   

                   

                  left join

                  load

                    Id_Recurso

                      ,Max(Month(Log_Periodo)) as MaxMonth

                  Resident Log

                  group by Año;

                    • Re: Cum count error
                      Jonathan Ruiz

                      I try this way


                      Log:

                      LOAD

                          AutoNumber(Id_Recurso) as Id_Recurso,

                          MonthName(Log_Periodo) as Periodo,

                          Num(Log_Periodo) as #Periodo,

                          Year(Log_Periodo) as Año,

                          Month(Log_Periodo) as Mes,

                          Ceil(Num(Month(Log_Periodo))/3) as Quarter   

                      FROM [lib://Acumulado/Log_Acumulado.qvd]

                      (qvd);

                       

                       

                      Left Join(Log)

                      Load

                          Max(Periodo) as Periodo,

                          1 as BanderaAño

                      Resident Log

                      Group by Año;

                       

                      Left Join(Log)

                      Load

                          Max(Periodo) as Periodo,

                          1 as BanderaQuarter

                      Resident Log

                      Group by Año,Quarter;



                      I can get the last month of the year and the quarter, but I would not know how to show both,


                      left.png


                        • Re: Cum count error
                          Vasiliy Beshentsev

                          Add max(quarter)

                          • Re: Cum count error
                            Vasiliy Beshentsev

                            Well you try to agregate counts into years but u want to do it not by the years, but by max(Month).

                            It looks like you need to do synthetic dimension with text of years: 2007, 2008, 2009, and calculate there count by max(Month/Quarter).

                            Read about ValueList and how it works. It helps me sometimes in not standart situations.

                              • Re: Cum count error
                                Jonathan Ruiz

                                I understand, thank you so much for your time Vasily, I'll read about it.

                                  • Re: Cum count error
                                    Jonathan Ruiz

                                    Arrive to the desired result, thanks to the tips of vasiliy_beshentsev, this was my solution


                                    Code:


                                    Log:

                                    LOAD

                                        AutoNumber(Id_Recurso) as Id_Recurso,

                                        MonthName(Log_Periodo) as Periodo,

                                        Num(Log_Periodo) as #Periodo,

                                        Year(Log_Periodo) as Año,

                                        Month(Log_Periodo) as Mes,

                                        Ceil(Num(Month(Log_Periodo))/3) as Quarter   

                                    FROM [lib://Acumulado/Log_Acumulado.qvd]

                                    (qvd);

                                     

                                    Left Join(Log)

                                    Load

                                        Max(Periodo) as Periodo,

                                        1 as BanderaAño

                                    Resident Log

                                    Group by Año;

                                     

                                    Left Join(Log)

                                    Load

                                        Max(Periodo) as Periodo,

                                        1 as BanderaQuarter

                                    Resident Log

                                    Group by Año,Quarter;


                                    Measure:

                                     

                                    if([BanderaAño]=1,Count({<[BanderaAño]={1}>}Id_Recurso),

                                      if(BanderaQuarter=1,Count({<[BanderaQuarter]={1}>}Id_Recurso),

                                        Count(Id_Recurso)   

                                    ))