topic Re: Evaluate function on dimension values in QlikView

Evaluate function on dimension values

Fri, 20 Jan 2012 16:30:14 GMT

Hi,

I`m new in the community so thank you in advance.

I have a chart that shows user by year and user are grouped by some funcionality.

I use this function COUNT( {$<user=E({$<TypeId={2}>})>*<OUTPUT={1}>} DISTINCT user)

to exclude users that are TypeId=2 and has output=1 at any time. This situations can succes in diferent days separately, but I'd like count user that never been TypeId=2 (for each dimension value in the chart.)

This function evaluate user from a selection. If I select year 2011, function works great but if I don't has a selection made, the chart shows all years but this function evaluate the users for all history data. So if user A is only OUTPUT=1 on 2011 but in 2012 becomes TypeId=2 too, then is excluded by the funtion but I want to show this user as OUTPUT=1 for 2011.

Anyone knows how to get this function evaluated by values from the dimension? Or any similar function?

Thank you very much.

Rgds

Evaluate function on dimension values

pover — Fri, 20 Jan 2012 17:55:05 GMT

Set Analysis is evaluated before the expression considers the dimension values. To respect the dimension value you can use a Count(if(..)). For example,

Count(disinct if(typeId=2 and OUTPUT=1, user))

If the condition is not met, the if statement returns a null value and null value are not counted in the count function.

Karl

Evaluate function on dimension values

Sun, 22 Jan 2012 17:51:42 GMT

Thank you Karl, but this condition doesn't show expected results. Your suggestion only apply when both conditions occurs at same time and doesn't discard users that change the situation.

I want to get user that never had Type=2 and has OUTPUT=1 for time period used on analysis.

My data is loaded daily.

I'll try to exply the situation more accurately.

COLUMNS: Time User Type OUTPUT

April'11 User_A 2 0

April'11 User_B 1 1

April'11 User_C 1 1

May'11 User_A 2 1

May'11 User_B 1 1

May'11 User_C 2 0

With this example, if I analyze data monthly the expected results are:

April= 2 users (User_B & User_C) that OUTPUT=1 and never type=2

May= 1 user (User_B) that OUTPUT=1 and never type=2

But if the analysis is done by year the result should be: 2011=1 user (User_B) that OUTPUT=1 and never type=2

because User_C has Type=2 on May'11 although it hasn't Type=2 on April.

Using de Count( if( .....)) the analisys is done by register and User_C is counted as expected result because the condition is true on April.

I'd like to identify Users that never has Type=2 and has Output=1 base on time (year, quarter, month or day).

Any suggestion?

Re: Evaluate function on dimension values

Sun, 22 Jan 2012 17:52:21 GMT

Thank you Karl, but this condition doesn't show expected results. Your suggestion only apply when both conditions occurs at same time and doesn't discard users that change the situation.

I want to get user that never had Type=2 and has OUTPUT=1 for time period used on analysis.

My data is loaded daily.

I'll try to exply the situation more accurately.

COLUMNS: Time User Type OUTPUT

April'11 User_A 2 0

April'11 User_B 1 1

April'11 User_C 1 1

May'11 User_A 2 1

May'11 User_B 1 1

May'11 User_C 2 0

With this example, if I analyze data monthly the expected results are:

April= 2 users (User_B & User_C) that OUTPUT=1 and never type=2

May= 1 user (User_B) that OUTPUT=1 and never type=2

But if the analysis is done by year the result should be: 2011=1 user (User_B) that OUTPUT=1 and never type=2

because User_C has Type=2 on May'11 although it hasn't Type=2 on April.

Using de Count( if( .....)) the analisys is done by register and User_C is counted as expected result because the condition is true on April.

I'd like to identify Users that never has Type=2 and has Output=1 base on time (year, quarter, month or day).

Any suggestion?

Re: Evaluate function on dimension values

Sun, 22 Jan 2012 19:26:15 GMT

Maybe this old school function it could be useful:

sum (aggr (If (max(Type) <> 2 and max(output) <> 1, 1), <useddimensions>, user))

You need to define all used dimensions in the aggregate method, or use fieldvalue() to get dinamically.

And then if you are using in a table or crosstab, apply dimensionality() to calculate TOTALS.

Re: Evaluate function on dimension values

Mon, 23 Jan 2012 08:16:33 GMT

It works great marquitus!!

Thank you guys!!