topic Re: Expression Calculation on Pivot Table in QlikView

Expression Calculation on Pivot Table

Fri, 09 Jan 2015 13:43:41 GMT

Currently our head office (Johannesburg) charges our branches a 10% markup on cost. I have been task to determine what the branch's Cost of Sales would have been if it was at Johannesburg's Unit Cost.

I am stuggling to calculate this expression (expression highlighted in red below & attached)

Attached herewith is the Qlikview Model as well as the data (in excel format) on which my Qlikview model is based.

Please can you assist.

kind regards.\

Nayan

	Financial Year	2014
	Financial Period	1
Branch	Fisc.Per-Month	1 (May)
Bloemfontein	Sales Qty	798
	Sales	79,800
	Cost of Sales	43,890
	Gross Profit	35,910
	GP%	45.0%
	Unit Cost	55.00
	Johannesburg COS	0
Cape Town	Sales Qty	479
	Sales	47,900
	Cost of Sales	26,345
	Gross Profit	21,555
	GP%	45.0%
	Unit Cost	55.00
	Johannesburg COS	0
Johannesburg	Sales Qty	13,160
	Sales	1,316,000
	Cost of Sales	658,000
	Gross Profit	658,000
	GP%	50.0%
	Unit Cost	50.00
	Johannesburg COS	658,000

Re: Expression Calculation on Pivot Table

morganaaron — Fri, 09 Jan 2015 14:04:58 GMT

Hi Nayan,

Is the markup fixed? If so, surely the unit cost will always be 10% higher for you than for Joburg, so you can simply take whatever your cost of sales is and divide by 1.1 to give you the answer?

Apologies if I've understood wrong!

Edit: And if you need this to be correct for Joburg too, have the expression as:

f(Branch='Johannesburg', [Cost of Sales], [Cost of Sales] / 1.1)

Re: Expression Calculation on Pivot Table

Fri, 09 Jan 2015 14:10:30 GMT

Hi Aaron

Thank you for your reply. Our actual Qlikivew model that we work with , the mark-up % varies per product. Thats' the difficulty I'm face with.

No need to apologise , you understood correctly.

kind regards

Nayan

Re: Expression Calculation on Pivot Table

morganaaron — Fri, 09 Jan 2015 14:37:37 GMT

Hi Nayan,

This is by no means probably the best way of doing it, but another way of doing it allowing for different markups for each product would be:

Resident Load the Joburg Unit Costs:

Load
[Item Code],
[Cost of Sales] / [Sales Quantity] as [Unit Cost]
Resident Data
Where Branch = 'Johannesburg';

Then use an aggr statement to calculate the overall cost of the items sold at Joburgs unit cost by:

=Sum(Aggr(Sum([Sales Quantity]) * [Unit Cost], [Item Code], [Financial Year], [Financial Period], Branch))

Making sure you allow for all the variables you need to break it down by in the Aggr statement!

I've tested that and it's working with the data you sent over. Let me know if you need any further explanation - I've attached what I did here.

Re: Expression Calculation on Pivot Table

Fri, 09 Jan 2015 14:52:56 GMT

Hi Aaron

Thank you for your answer. I will test it our on our QV Model we use and will give you feedback on Monday as I'm about to leave for home.

kind regards

Nayan

Re: Expression Calculation on Pivot Table

Tue, 13 Jan 2015 08:34:58 GMT

Hi Aaron

Your formula works. Thank you for all your help. Much appreciated.

kind regards

Nayan