Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Specialist
Specialist

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.

View solution in original post

5 Replies
morganaaron
Specialist
Specialist

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

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

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.

View solution in original post

Not applicable

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

Hi Aaron

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

kind regards

Nayan