Calulated dimensions or ...

[beunderf]

Hello,

I have the following  question:

I have a couple of values a day. For every
day I want to calculate an average. Each day could have a score between 0 and
10. So far no problem.

Now I want to create (not yet existing
in the data) a dimension like this:

DayScore

0 < 3
3 < 6
6 < 9
9 or 10

Now I want to count how many day’s there are in every DayScore

How do I do this, while the dimensions does not exist in the dataset (please see attachment for an example set)

[#2]

add a macro:

Tools -> Edit module:

function checkdays(value)

if value<3 then

                    checkdays="0<3"

elseif value<6 then

                    checkdays= "3<6"

elseif value<9 then

                    checkdays= "6<9"

else

          checkdays ="9 or 10"

end if

end function

then change your load script:

Data:

LOAD * INLINE [

    ID, DAY, VALUE

    1, 1, 2

    2, 1, 5

    3, 1, 8

    4, 1, 9

    5, 1, 9

    6, 2, 6

    7, 2, 8

    8, 2, 7

    9, 2, 6

    10, 2, 9

    11, 3, 5

    12, 3, 8

    13, 3, 9

    14, 3, 9

    15, 3, 6

    16, 4, 4

    17, 4, 5

    18, 4, 6

    19, 4, 4

    20, 4, 6

];

Avg:

Left Join(Data)

Load DAY,

          checkdays(avg(VALUE)) as DayAvg

resident Data group by DAY;

now you create a chart with DayAvg as dimension and count(distinct DAY) as expression

[daveamz · Accepted solution]

Hi Frank,

     You can also use a calculated dimension and then count distinct day.

See the example attached.

Regards,

David

[beunderf]

David,

I think the calulated dimension is the way to go. My problem is, that it is a hughe dataset. Calculated dimensions always use a lot of CPU time.

There is one more thing, 0<3 and ' 9 and 10'  are not shown, because of the zero value. Do you know how I can show them, even when the value is 0?

frank

[beunderf]

Simon,

Your solution is helpful, but of course my example is a simplification of the truth. I made it simple by having just one dimension 'DAY'  But in fact, the table has more dimensions. Day, Type, Color, ect.. The graph needs to recalculate the values for every selection I make. With your solution the result is hard coded in the script.

Frank

[daveamz]

That is true, the if function will slow CPU a lot.

You can try to calculate the avg within the script using group by, then make an intervalmatch by result.

It should be faster then 'if' function but still it won't display the zero value.

Also, watch out for duplicate records when using intervalmatch, if avg(DAY)= 3 it is possible to link with 0<3 and also 3<6. To avoid that you should set min/max like: 0, 3 then 3.00001, 6. I haven't done that because of Thousand/Decimal separator.

value:

LOAD * INLINE [

    ID, DAY, VALUE

    1, 1, 2

    2, 1, 5

    3, 1, 8

    4, 1, 9

    5, 1, 9

    6, 2, 6

    7, 2, 8

    8, 2, 7

    9, 2, 6

    10, 2, 9

    11, 3, 5

    12, 3, 8

    13, 3, 9

    14, 3, 9

    15, 3, 6

    16, 4, 4

    17, 4, 5

    18, 4, 6

    19, 4, 4

    20, 4, 6

];

score:

load * Inline [

min, max, scorename

0, 3, '0<3'

3, 6, '3<6'

6, 9, '6<9'

9, 10, '9 or 10'

];

values2:

    load

        avg(VALUE) as SCORE, DAY Resident value Group by DAY;

left join (values2) IntervalMatch (SCORE) load min,max Resident score;

left join (values2) load * resident score;

drop Table score;

Good luck!

[beunderf]

I am still strugeling with the fact it is not showing on the x-axe when the value is zero. I am using the calculated dimensions. Any idea??

[daveamz]

Good morning Frank,

     I've made it with zero values, but it will work only if you'll use the intervalmatch variant. I don't know if this is the best way to do it but, it seems to work with this test data set.

Regards,

David