2 Replies Latest reply: Mar 1, 2013 5:37 AM by Thorsten Schröder RSS

    Excel Pivot to QlikView

    Thorsten Schröder

      Hello Community,


      I'm struggling to transfer my Excel pivot reports to QlikView.


      The data comes from a sql server DB and I use a variable to calculate the maturity in days depending on two columns.

      In Excel this is an extra column and looks like this translated to QlikView:


      set vMaturityInDays = Num(Date([NetMaturity]))-Num(Date([CutoffDate]))


      I have set up another extra column ('not due') in Excel to show the different range of days depending on conditions.


      I transfered the Excel formula to QlikView like so:

      if ($(vMaturityInDays) >=0,'not due',if($(vMaturityInDays) >=-30,'<=30d',if($(vMaturityInDays)>=-90,'30d><=90d',if($(vMaturityInDays) >=-180,'90d> <=180d','>180d'))))


      In Excel I can then take the 'not due' column and add it to the column filter in my pivot and I'm presented with this:mat.PNG

      The general calculation works within QlikView but I can't figure out how to transfer the splitted day ranges above over to QlikView.

      Any ideas?


      thank you.

        • Re: Excel Pivot to QlikView
          Thorsten Schröder

          still looking for some help on this topic.


          I've created a sample application, please have a look:


          The resulting QlikView pivot table should look like the following screenshot of the Excel pivot.

          The numbers below represent the Amount/1000 sum value for the various time periods.



          Any idea how to get this done? Or can you recommend different approach?

          Thank you.

            • Re: Excel Pivot to QlikView
              Thorsten Schröder

              So I've managed to get the look like I wanted for my pivot.

              Inside the LOAD stmt I put:


              if (Num(Date([NetMaturity]))-Num(Date([CutoffDate])) >=0,'not due',if(Num(Date([NetMaturity]))-Num(Date([CutoffDate])) >=-30,'<=30d',
              if(Num(Date([NetMaturity]))-Num(Date([CutoffDate]))>=-90,'30d><=90d',if(Num(Date([NetMaturity]))-Num(Date([CutoffDate])) >=-180,'90d><=180d','>180d')))) 
              AS myDueInfo


              Then I added the following expression(s) to my pivot:


              if (myDueInfo LIKE '<=30d',sum(Amount) , 0)


              if (myDueInfo LIKE '30d><=90d',sum(Amount) , 0)


              but this only calculates the values when I click on the according value of "myDueInfo".

              However I'd like the calculation to work without any selection, so that I can get an overview for all time periods.


              What do I need to do for this?