10 Replies Latest reply: Oct 20, 2017 12:20 PM by Sunny Talwar RSS

    Date and Percentage in PivotTable

    Guilherme Cidade

      Good afternoon,


      I need your help to solve this app in QlikView.

      The application template is a bit confusing, so if you have any suggestions, you are welcome.

      I also don't know if it is possible to do in this way I need.


      I load the data with the following script: (It is in portuguese, so I wont translate the fields to you understand my expressions)

      BaseCard:

      LOAD
      DATAINCLUSAO,                                        //here shows the full date when the card was created
      Month(DATAINCLUSAO) as MêsInclusão,
      Year(DATAINCLUSAO) as AnoInclusão
      Monthname(MonthStart(DATAINCLUSAO)) as [Mês Ano],
      SITUACAO,                                              //here is as A(Active),B(Block) e C(canceled)
      DATASITUACAO,                                    //here shows the date when the situation changed
      DATABLOQUEIO,                                  //here shows the block date of the card
      SITUACAOANTERIOR,                        // here shows the last situation
      DATASITUACAOANTERIOR,              // here shows the date of the last situation

      FROM
      [C:\Cloud...]

       

      In a PivotTable, I want to know the month / year of the situation, the quantity that was made, qty after 3 months, 6 and 12 months, and the %.

      I used the following as a dimension:

      1.png

       

      The calculated dimension is an 'if' to show the name of the situation.


      In the expressions, I used the following:

      For %:

      count (SITUACAO) / Count (total (SITUACAO))

       

      Qty:

      count ([Year Month])

       

      The way I'm doing both% and qty do not show the values I want.

      The amount I want shows the value of cards for your situation (active, block, canc), for the month that was effectived.

      For example: I can have a card that its inclusion date was 09/20/2016 but it was only unlocked on 12/20/2016, so I want it to count as produced in the month / year of inclusion (Sep / 2016) and appears as blocked. When I look at it after 3 months, I will see the same amount of cards made, but only your situation will change.

      2.png

      Note that the data after 3 months for sep, would be dec 2016. There it shows the cards that had as inclusion date the month of September, but were only unlocked in December.

      The second line (December) also shows December 2016, but it shows only the cards that had as inclusion date from Dec / 2016 and were unblocked, or canceled in the month, after 3 months, etc ...

      I want my application to look like the excel above, but I could only achieve this:

      3.png

      What should I do to get the model I need?

        • Re: Date and Percentage in PivotTable
          Sunny Talwar

          Is Situacao a calculated dimension? Can you perform it in the script?

          • Re: Date and Percentage in PivotTable
            Sunny Talwar

            I am attaching an Excel for you (exported from your app) where I have pointed out current calculation and I want you to point me out what the expected calculation is

             

            Capture.PNG

             

            Currently, all I am doing is that for a particular Situação, I am pulling three months back numbers. For instance, if you look at Cartão Ativo Bloqueado for Set 2016 = 1581 (Column F, Row 59) which is the same number we see for jun 2016 (Column E, Row 47).

             

            Now all I want you to do is to fill column G with what you think should be the correct number

            • Re: Date and Percentage in PivotTable
              Sunny Talwar

              I would have deleted the qvw.... you removed some of the important information from this post. Really feel bad that I spent so much time helping you out...

                • Re: Date and Percentage in PivotTable
                  Guilherme Cidade

                  I tried to delete just the qvw but it excluded everything. Why this happened?

                    • Re: Date and Percentage in PivotTable
                      Sunny Talwar

                      Because you have to edit the post and then remove the attachment rather than just deleting everything. It is just disappointing to see that after so much help that I was trying to offer, all have been deleted. Anyways please close this thread at least by marking Assumed Answer

                      Qlik Community Tip: Marking Replies as Correct or Helpful

                        • Re: Date and Percentage in PivotTable
                          Guilherme Cidade

                          I'm really sorry for that. I have the conversation in my email.. I'll post it .

                          Sorry for my error again.

                          • Re: Date and Percentage in PivotTable
                            Guilherme Cidade


                            Sunny , These are your answers. Please just copy and paste again to you receive the credit for that.


                            Is this what you wanted?

                             

                            https://community.qlik.com/servlet/JiveServlet/downloadImage/2-1360089-179272/Capture.PNG

                             

                            Expression

                            If(CrtCartoesPlasticos_SITUACAO = 'B', Count(TOTAL <[MêsInc AnoInc]> CrtCartoesPlasticos_SITUACAO) - Count(TOTAL <[MêsInc AnoInc]>If([MêsInc AnoInc]=[MêsSit AnoSit],CrtCartoesPlasticos_SITUACAO)),

                            If(Match(CrtCartoesPlasticos_SITUACAO, 'A', 'C'), Count(If([MêsInc AnoInc] = [MêsSit AnoSit],CrtCartoesPlasticos_SITUACAO)), Count(CrtCartoesPlasticos_SITUACAO)))

                            -----------------


                            Check this

                             

                            Sum(Aggr(

                            If(CrtCartoesPlasticos_SITUACAO = 'B',

                            Count(TOTAL <[MêsInc AnoInc]> CrtCartoesPlasticos_SITUACAO) - Count(TOTAL <[MêsInc AnoInc]>If([MêsInc AnoInc]=[MêsSit AnoSit],CrtCartoesPlasticos_SITUACAO)),

                            Count(If([MêsInc AnoInc] = [MêsSit AnoSit],CrtCartoesPlasticos_SITUACAO))), [MêsInc AnoInc], CrtCartoesPlasticos_SITUACAO))

                            https://community.qlik.com/servlet/JiveServlet/downloadImage/2-1360301-179284/Capture.PNG



                            ---------------------

                            This looks good now?

                             

                            https://community.qlik.com/servlet/JiveServlet/downloadImage/2-1362279-179696/Capture.PNG

                             

                            Expression used:

                            Sum(Aggr(

                            If(CrtCartoesPlasticos_SITUACAO = 'B',

                            Count(TOTAL <[MêsInc AnoInc]> CrtCartoesPlasticos_SITUACAO) - Count(TOTAL <[MêsInc AnoInc]> {<CrtCartoesPlasticos_SITUACAO = {'A', 'C'}>}If([MêsInc AnoInc]=[MêsSit AnoSit],CrtCartoesPlasticos_SITUACAO)),

                            Count(If([MêsInc AnoInc] = [MêsSit AnoSit],CrtCartoesPlasticos_SITUACAO))), [MêsInc AnoInc], CrtCartoesPlasticos_SITUACAO))

                              • Re: Date and Percentage in PivotTable
                                Sunny Talwar

                                Guilherme

                                 

                                Thanks for making amend of this... but what you have above won't help future visitors... There is a flow to a discussion which helps future visitors understand what the direction of this discussion is. By just adding random expressions, no one will understand what was going on here.

                                 

                                Anyways, what has happened has happened and I see that your intentions were not wrong. I would just tell you that try not to do this again in the future as we loose good information when people delete stuff.

                                 

                                Only thing I would suggest now is to close this thread (I have asked multiple times now) and have also given you directions on how to do this.

                                 

                                Qlik Community Tip: Marking Replies as Correct or Helpful

                                 

                                Once again, by doing this, you will be helping the community as people who are looking to help will know that this thread has been resolved and they don't have to stop by for help. For future visitors, this might just be a lost cause, but that's okay.