33 Replies Latest reply: Oct 18, 2017 9:40 AM 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?