How to achieve the below task?

[narayanamsn]

Hi all,

I have the below scenario. Have a set of values for 3 months.

I want to get a count of values which are less than the average of all values for the month..

LOAD * INLINE [

    Month, Sale

    Jan, 2

    Jan, 9

    Jan, 4

    Jan, 5

    Jan, 6

    Jan, 8

    Jan, 1

    Feb, 2

    Feb, 3

    Feb, 4

    Feb, 5

    Feb, 8

    Mar, 6

    Mar, 8

    Mar, 1

    Mar, 2

    Mar, 3

];

How to plot a graph with Month as dimension and Count of values that are less than monthly average as a measure...

Avg of Jan 5, Feb 6, Mar 4 and overall is 5

The observation that I got when I wrote the code is it is for Feb it is not comparing the value against average of Feb (6) but comparing the values against the overall average 5..

The same is observed for all the aggregation functions.. Instead of comparing with the measure of that period , gets compared with the measure of all the time period..

How to avoid this behavior..

Regards,

Satya

[sunny_talwar]

I am getting a different average for Feb = 4.4?

[sunny_talwar]

Try this script:

Table:

LOAD Month(Date#(Month, 'MMM')) as Month,

  Sale,

  RowNo() as Key;

LOAD * INLINE [

    Month, Sale

    Jan, 2

    Jan, 9

    Jan, 4

    Jan, 5

    Jan, 6

    Jan, 8

    Jan, 1

    Feb, 2

    Feb, 3

    Feb, 4

    Feb, 5

    Feb, 8

    Mar, 6

    Mar, 8

    Mar, 1

    Mar, 2

    Mar, 3

];

Dimension: Month

Expression:

=Count({<Key = {"=Sale < Avg(TOTAL <Month> Sale)"}>}Sale)

[narayanamsn]

Hi Sunny,

Thank you for the rocket speed response. Can you explain the evaluation of the formula...

Alternate

I created a variable for average vAvg and want to pass this instead of writing the avg formula in the if statement..

How to modify your formula to accommodate the variable..

I have another scenario with Fractile function.. Hence thought of understanding your expression properly...with variable

Regards,

Satya

[sunny_talwar]

For each row (using RowNo() I have created a unique row), I am checking if Sale is less than Avg of sale for the month (Avg(TOTAL <Month> Sale)). If it is, then include the key, otherwise don't include it.

Does it make sense?

[narayanamsn]

absolutely.. It makes sense.. I am trying to get the same result using variable !

Script..

Table:

LOAD Month(Date#(Month, 'MMM')) as Month,

  Sale,

  RowNo() as Key;

LOAD * INLINE [

    Month, Sale

    Jan,2

    Jan,9

    Jan,4

    Jan,5

    Jan,6

    Jan,8

    Jan,1

    Feb,10

    Feb,27

    Feb,36

    Feb,45

    Feb,7

    Mar,25

    Mar,70

    Mar,50

    Mar,30

    Mar,5

    ];

Variable vAvg= Avg(Sale)

Below is the image.. I need to use variable in the set analysis to fetch data ..Is it possible..

vAvg is always 20 in this case (irrespective of the month.) but my request is how to get the same values that you plotted in left table using the variable..

[sunny_talwar]

Check the updated file with the variable created

[narayanamsn]

Fantabulous..

One thing , I am unable to understand is why to update Key = in set analysis..

what does it mean. (we are comparing sales value with Avg sales and what role does Key= play here..

Count({<Key = {"=Sale < $(vAvg)"}>}Sale)

[sunny_talwar]

In order to do a unique combination check, we need to have a unique key which  can uniquely associate a month sale number to the Avg for the month. In this sample it was as simple as creating a unique key. In another instance you might be able to create another unique combination.

To see this, create a straight table with Key as your dimension and three expressions

1) Sale

2) $(vAvg)

3) =If(Sale < $(vAvg), Key)

You will see that the third expression will show only those keys which needs to be counted.

I hope this will help clarify some of the doubts

[narayanamsn]

Hi Sunny,

The solution is not working for the Fractile function. Can I have chat with you...