Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
Hi Frank,
You can also use a calculated dimension and then count distinct day.
See the example attached.
Regards,
David
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
Hi Frank,
You can also use a calculated dimension and then count distinct day.
See the example attached.
Regards,
David
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
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
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!
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??
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