9 Replies Latest reply: Oct 10, 2011 6:57 AM by gboyland

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

Thanks

 Inmonth Budget Actual Variance Income 500 550 -50 Expenditure 450 400 50 EBITDA Income - Expenditre ITD 50 55 -5 Depreciation 45 40 5 Before IFRIC12 ITD - Depreciation IFRIC12 40 40 0 After IFRIC12 Before IFRIC12 - IFRIC12

Thanks

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

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

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

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

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

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

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

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.

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