Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculate sales distribution

Hi there,

I want to create a calculated field at load or master items measure that shows sales distribution by fiscal yr.

As the data spans multiple years this will need to be taken into account.

I thought it would be as simple as Sales Amount/Sum(Sales Amount) however this doesn't work.

I have implemented a master calendar that has a fiscal year field.

thanks

James

6 Replies
lakshmikandh
Specialist II
Specialist II

You have to use Sum(Sales_Amount)/Sum(TOTAL Sales_Amount)

Total keyword will do Sum at all level

In case you want to ignore a specific dimension (i.e) if you have Product,Region,Month as dimension

You want to ignore Product and do sum for all region and all months

you have to use like Sum(Sales_Amount)/Sum(Total Product Sales_Amount)

Hope this helps

lakshmikandh
Specialist II
Specialist II

jyothish8807
Master II
Master II

Hi James,

What is your dimension in this case ?

Since you have master calendar implemented this should  be easy.

For ex:

Dimension: Year

Expr:

Sum(Sales_Amount)/Sum(TOTAL Sales_Amount)

Regards

KC

Best Regards,
KC
Anil_Babu_Samineni

Why do you require master calendar, Would you elaborate more. Try this

[Sales Amount]/Sum([Sales Amount])

OR

[Sales Amount]/Sum(TOTAL [Sales Amount])

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable
Author

thanks Lakshmikandh...I have gone with the following;

Sum(Sales_Amount])/Sum({1} Total [Sales_Amount])

Not applicable
Author

Hi KC,

I'm aiming to use Financial/Fiscal Yr as the primary dimension, then slice & dice by multiple dimensions from there. I was able to get the distribution per Financial/Fiscal Yr but when using other dimensions the distribution remains @ 100%. I've attached a few screen shots to explain;

Sales Distribution % for 2016 is 78.7% (of Total Sales)

QS-Dist1.png

Selecting a Business Unit, 2016 FY Distribution remains at 100%

QS-Dist2.png

hope this makes sense

thanks

James