topic Re: Calculate sales distribution in QlikView

Calculate sales distribution

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Calculate sales distribution

lakshmikandh — Fri, 07 Oct 2016 05:52:51 GMT

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

Re: Calculate sales distribution

lakshmikandh — Fri, 07 Oct 2016 05:55:05 GMT

For More information regarding Aggregation Scope

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

Re: Calculate sales distribution

jyothish8807 — Fri, 07 Oct 2016 06:27:44 GMT

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

Re: Calculate sales distribution

Anil_Babu_Samineni — Fri, 07 Oct 2016 07:09:18 GMT

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

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

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

Re: Calculate sales distribution

Mon, 10 Oct 2016 00:10:15 GMT

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

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

Re: Calculate sales distribution

Mon, 10 Oct 2016 00:26:57 GMT

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