Calculate sales distribution

[#1]

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

[lakshmikandh]

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]

For More information regarding Aggregation Scope

Check this https://help.qlik.com/en-US/sense/3.1/Subsystems/Hub/Content/ChartFunctions/define-aggregation-scope...

[jyothish8807]

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

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

[#6]

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

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

[#7]

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)

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

hope this makes sense

thanks

James