Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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?
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?
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.
It works great marquitus!!
Thank you guys!!