Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Excel Pivot to QlikView

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.

2 Replies
Not applicable

Re: Excel Pivot to QlikView

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.

Not applicable

Re: Excel Pivot to QlikView

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?