Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Not applicable

Excel like reporting in Qlikview - Row 3 = Row 1 - Row 2

Hi,

I want to replicate a excel report in qlikview. Data comes from Oracle, previous they were manually computing it in Excel now i have to replace that manual computing in qlikview.

For example:

Row 1 is Income

Row 2 is Expenditure

Row 3 = Income - Expenditure

Row 4 is Depreciation

Row 5 = Row3 - Row4

I have also attached the excel sheet of what i'm trying to say.

Please help me.

Thanks

Widaad

Inmonth
BudgetActualVariance
Income500550-50
Expenditure45040050
EBITDAIncome - Expenditre
ITD5055-5
Depreciation45405
Before IFRIC12ITD - Depreciation
IFRIC1240400
After IFRIC12Before IFRIC12 - IFRIC12


Thanks

Widaad

1 Solution

Accepted Solutions
matt_crowther
Valued Contributor

Excel like reporting in Qlikview - Row 3 = Row 1 - Row 2

Ahh - the wonderful world of Finance Reporting in Qlikview - it's the bain of my life.

The best example I've found is from the Share Qlikviews page: http://community.qlik.com/media/p/122153.aspx It's probably more complex than you need (or expected) but should provide you with some pointers - the Excel 'control sheet' is by far the best method I've found.

I also came up with a similar method using the rangesum() and above() functions so for your row 3 you'd have something similar to rangesum(above(Sales,2)) with the 2 being assigned to the subtotal via a value in the script and also having a 'level' designation to control which expression to use...a total pain beginning to end.

This is the area that Qlikview really falls down on - or doesn't even try to achieve; you can get there as the above shows but it's nigh on un-workable and very time consuming. One other option would be to look at QvExcel from Industrial Codebox which allows you to handle the Excel style formatting in Excel which is surely the best place for it!?

Hope that helps and Good Luck!

Matt - Visual Analytics Ltd

9 Replies
schlettig
Contributor

Excel like reporting in Qlikview - Row 3 = Row 1 - Row 2

you can't do everything exactly like in excel, but:

you can use the column() function.

like Column(2) - Column(1)

maybe that helps.

Best regards,

Christian

matt_crowther
Valued Contributor

Excel like reporting in Qlikview - Row 3 = Row 1 - Row 2

Ahh - the wonderful world of Finance Reporting in Qlikview - it's the bain of my life.

The best example I've found is from the Share Qlikviews page: http://community.qlik.com/media/p/122153.aspx It's probably more complex than you need (or expected) but should provide you with some pointers - the Excel 'control sheet' is by far the best method I've found.

I also came up with a similar method using the rangesum() and above() functions so for your row 3 you'd have something similar to rangesum(above(Sales,2)) with the 2 being assigned to the subtotal via a value in the script and also having a 'level' designation to control which expression to use...a total pain beginning to end.

This is the area that Qlikview really falls down on - or doesn't even try to achieve; you can get there as the above shows but it's nigh on un-workable and very time consuming. One other option would be to look at QvExcel from Industrial Codebox which allows you to handle the Excel style formatting in Excel which is surely the best place for it!?

Hope that helps and Good Luck!

Matt - Visual Analytics Ltd

chrisbrain
Valued Contributor

Excel like reporting in Qlikview - Row 3 = Row 1 - Row 2

If you would like an online demo of QVExcel please feel free to contact me directly - you can also see some sample financial reports at:
http://qvexcel.com/screenshots

And also download a free evaluation of QVExcel there.

Not applicable

Excel like reporting in Qlikview - Row 3 = Row 1 - Row 2

Hi Widaad

The problem is that excel is cell based while qlikview is column(row) based.

Try to load the data and look at the transformation step to there pre-process the data so you easily can calculate it.

I would suggest you pivot the data to get the rows as column and visverse.

Not applicable

Excel like reporting in Qlikview - Row 3 = Row 1 - Row 2

Hi Matt,

Yes this is what i was looking for but it bit more simple than what i want. Still this will help in acheiving the result.

Thanks a lot for this.

I'm able to produce what i want now but Format of the totals is working in the sample qvw but when i use the same in mine it does not bold or italise the row, i dont understand why?

Could you please help me in that. I have also used the same table PLFormat in my qvw and joined it to the type column as in PLData table.

Thanks

Widaad

Not applicable

Excel like reporting in Qlikview - Row 3 = Row 1 - Row 2

Hi Chris,

This QVExcel does lot of help in excel reporting but due to security and other stuffs i didn't get clearance to use this.

Thanks a lot

Widaad

Not applicable

Excel like reporting in Qlikview - Row 3 = Row 1 - Row 2

Hi


This post is a few months old now I'm interested in looking at the example that Matt has referred to in his reply post. The link to the Share QlikViews page no longer works. Does anyone know where I can get hold of the example that he referred to http://community.qlik.com/media/p/122153.asp?

Thanks, Kevin

Not applicable

Excel like reporting in Qlikview - Row 3 = Row 1 - Row 2

Not applicable

Excel like reporting in Qlikview - Row 3 = Row 1 - Row 2

You can use set analysis functions as calculated dimensions and set the table to be horizontal

this will give you your desired look and structure

hope this helps

G

Community Browser