Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Expression Calculation on Pivot Table

Hi

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 Year2014
Financial Period1
BranchFisc.Per-Month1 (May)
BloemfonteinSales Qty798
Sales79,800
Cost of Sales43,890
Gross Profit35,910
GP%45.0%
Unit Cost55.00
Johannesburg COS0
Cape TownSales Qty479
Sales47,900
Cost of Sales26,345
Gross Profit21,555
GP%45.0%
Unit Cost55.00
Johannesburg COS0
JohannesburgSales Qty13,160
Sales1,316,000
Cost of Sales658,000
Gross Profit658,000
GP%50.0%
Unit Cost50.00
Johannesburg COS658,000
1 Solution

Accepted Solutions
morganaaron
Valued Contributor

Re: Expression Calculation on Pivot Table

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.

5 Replies
morganaaron
Valued Contributor

Re: Expression Calculation on Pivot Table

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)

Not applicable

Re: Expression Calculation on Pivot Table

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

morganaaron
Valued Contributor

Re: Expression Calculation on Pivot Table

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.

Not applicable

Re: Expression Calculation on Pivot Table

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

Not applicable

Re: Expression Calculation on Pivot Table

Hi Aaron

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

kind regards

Nayan

Community Browser