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.

      col.PNG

      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.

          excelPivot.PNG

           

          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:

              Example:

              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?