topic Excel Pivot to QlikView in QlikView

Excel Pivot to QlikView

Tue, 19 Feb 2013 11:54:19 GMT

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:

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

Wed, 27 Feb 2013 13:37:35 GMT

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

Fri, 01 Mar 2013 10:36:55 GMT

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?