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

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

Accepted Solutions
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:

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

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:

[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

Hi Aaron