15 Replies Latest reply: Oct 2, 2015 5:05 PM by Bhushan Nirgude

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

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

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

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

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

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

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

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

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

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.

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

To get the above add Month as a dimension and change variables to add Month in the aggr function..

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

Hi Bhushan,

I've made some final modifications to the concept I've been describing (attached).

There is a chart that calculates the min/max value over the entire timeframe selected.

And there is another chart that calculates the min/max value for each month to determine the scale.

Let me know if this helps.

Thanks,

Camile

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

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

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

Please use the Object Properties on the Axes tab.

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

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

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

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-max-value

and uses cuv 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.

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

Hi Camile ,

I am just working on the suggestion provided by you .

For me there is no Customers , as all my calculations are no monthly . So i m just remving customers from your equation.

My final outcome is line chart of all my expressions within 1 to 10 for last 15 months ....

I m still struggling in getting functional value by teh logic u given * i.e. max & min ).. so debugging the same .

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

So from the example above you should just be able to switch Member with Month for the variable values....

Just use the first attachment I put together (only by Member) and switch the variable from Member to Month (and switch the dimension from Member to Month as well).

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

Thanks a zillion Camile J ,

It works for me .... !!!!!

really appreciating power of Community ..

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

Hi Bhushan,

I'm glad it was able to work.

I'm not sure if you can change it or not -- but can you instead mark the answer with the attachment as correct as I think it will be more useful for people trying to find/use a working example.

Thanks,

Camile