How to scale all expressions into 1 to 10 scale on Y - axis

[#1]

Hi All ,

I have this bit different requirement from my client :

I am calculating monthwise Sales , Qty , Avg bill Value , Avg product per bill and showing it as a line chart separately .

Now client's requirement is to see all in one chart and that to on scale of 1 to 10 .

In simple way minimum of all expressions (i.e. sales , Qty , Avg bill value, Avg products per bill)  has to be considered 1 , while maximum of all expressions has to be considered 10 . Now all values of all expressions lies between 1 to 10 & then it needs to show case as a line chart .

Looking for help here ASAP .

Thanks

[cjohnson]

Hi Bhushan,

Is this requirement dynamic or is it static? For example - as the user makes selections the minimum and maximum value will change -- will it be re-scaled (from 1 to 10) based on selections or is the minimum and maximum value based on what is originally defined in the script (with all selections cleared).

Thanks,

Camile

[Clever_Anjos]

Maybe expressions like this can do what you need

SUM(Sales) / Sum(TOTAL Sales) // This return a number between 0 and 1

ceil(10* SUM(Sales) / Sum(TOTAL Sales)) // This return a number between 1 and 10

[#4]

I believe you need to use the Fixed scale for all expressions if you use stack Bar graph

[#5]

Please use the Object Properties on the Axes tab.

[cjohnson]

[#7]

Hi Clever Anjos ,

Cheers !!   .. its very close to finish .

" ceil(10* SUM(Sales) / Sum(TOTAL Sales))  " looks working fine , but there is one problem in that .

I hv data of 3 years while monthly chart which i mentioned is for last 15 months only , so how sum( tOTAL sales) can be modified to dimension range defined  ( here its 15 months) ??

ALso, how I can take this to for avg bill value which is sum(Sale) / sum ( bills) for particular month 

[cjohnson]

Hi Bhushan,

I've incorporated a few of the ideas mentioned above in an example. The first column highlighted below indicates the use of the ceiling function mentioned above.

I have also used a function below (second column highlighted) with the following function model:

f(x) = (b - a)(x - min)    +  a

          max - min

In your example a = 1 and b = 10, but it could be substituted with any scale. I have listed the minimum sum by the dimension (which is stored in a variable called vMinValue). There is also a vMaxValue by the same dimension (in this case called Member). I'm not sure if this is your intent.

Please see attached for more details.

Thanks,

Camile

[cjohnson]

The above example uses the following concept:

http://stackoverflow.com/questions/5294955/how-to-scale-down-a-range-of-numbers-with-a-known-min-and...

and uses cleveranjos‌ idea mentioned below:

Maybe expressions like this can do what you need

SUM(Sales) / Sum(TOTAL Sales) // This return a number between 0 and 1

ceil(10* SUM(Sales) / Sum(TOTAL Sales)) // This return a number between 1 and 10

For the function you would need to modify your min and max variables accordingly.

[cjohnson]

Is it based on the min/max value for the month or the min/max value over the entire period? See attached (and explanation below) for min of the month (over entire time frame selected). All you would have to do in that case is change the variable value to incorporate Month as part of the aggr function. To make the min/max value be within each month/dynamic you would need something a little different.