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

[#1]

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

Widaad

[schlettig]

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 · Accepted solution]

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]

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.

[#5]

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.

[#6]

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

[#7]

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

[#8]

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

[#9]

http://www.qlikviewexamples.com/2010/07/creating-excel-driven-profit-loss.html

[#10]

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