topic Re: Calulated dimensions or ... in QlikView

Calulated dimensions or ...

beunderf — Tue, 21 Aug 2012 11:45:18 GMT

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)

Re: Calulated dimensions or ...

Tue, 21 Aug 2012 11:57:07 GMT

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

Re: Calulated dimensions or ...

daveamz — Tue, 21 Aug 2012 12:27:12 GMT

Hi Frank,

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

See the example attached.

Regards,

David

Re: Calulated dimensions or ...

beunderf — Tue, 21 Aug 2012 12:34:43 GMT

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

Re: Calulated dimensions or ...

beunderf — Tue, 21 Aug 2012 12:44:29 GMT

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

Re: Calulated dimensions or ...

daveamz — Tue, 21 Aug 2012 13:16:21 GMT

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!

Re: Calulated dimensions or ...

beunderf — Tue, 21 Aug 2012 14:25:29 GMT

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??

Re: Calulated dimensions or ...

daveamz — Wed, 22 Aug 2012 08:05:04 GMT

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