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

# 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.

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

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

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?