Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I would appreciate some assistance with the following problem.
Objective
I am trying to exclude certain dimension records based on the results of an IF statement. The way that I am doing this is to set the EmpName where a Count is > 5 ELSE set the EmpName to NULL. I then exclude Null values from the straight table chart.
Problem
I receive the message "// Error in calculated dimension" when I create a Calculated Dimension using the following statement:
=if(count({total <CurrentYear=CurrentYear, EmpName=EmpName>} TotSals) > 5, EmpName)
However, if I use the same statement to set the Text Format to Bold in the Calculated Dimension then the statement works and the Text is set to Bold.
=if(count({total <CurrentYear=CurrentYear, EmpName=EmpName>} TotSals) > 5, '<B>')
I don't understand how the statement is evaluating correctly for setting the Text Format but not for the overall Dimension level? I have also tried a few variations such as forcing null() into the "else" part of the IF statement but I have not yet been able to get this to work.
Thanks in advance.
Regards,
Craig
Hi, it is because you are trying to base a dimension on a measure/ result of an aggregate try something like this:
=aggr( if(count({total <CurrentYear=CurrentYear, EmpName=EmpName>} TotSals) > 5, EmpName) , EmpName)
Thanks
Steve
Hi Steve,
Thanks for your help. I tried what you suggested but I am still not getting the results that I need as I don't think I explained the problem clearly enough.
Your solution works if I only have a CurrentYear selected i.e. it shows only those records with a TotSals count > 5.
However, even though I am using "total", if an EmpName is selected in addition to CurrentYear then the results are limited to only the single selected EmpName if the count of their TotSals > 5.
I'm not familiar with the AGGR function so I think that the problem is how I am using set analysis within the statement.
A worded version of what I am trying to do is:
Show all Employees (EmpName) who have Total Sales (TotSals) of more than 5 for only the Selected Year (Current Year) regardless of whether or not a single Employee (or any other field) has been selected.
Thanks again,
Craig
I think i see what you mean, try something like this:
= AGGR( if( count( { 1<CurrentYear={$(=only({$} CurrentYear))}>} TotSals) >5,EmpName ) , EmpName)
Thanks
Steve
Thanks very much Steve - I have tried that but I only get the correct results if CurrentYear is the only item selected. As soon as I select an Employee then the results in the chart are limited to that person only as opposed to showing all employees across the data set for that year.
I would appreciate it if you could have a look at the attached example of the problem and let me have any ideas.
Regards,
Craig
Hi, Try this.
Thanks
Steve
If you mean by Total Sales > 5 is sum(totSales) then try the below expression:
=if(AGGR(sum({<EmpName=>}TotSals),EmpName)>5,EmpName)
Also attached the modified document.
Regards,
Kiran
Hi
try this out
=if(aggr(count(EmpName),CurrentYear) > 5,EmpName,Null())
Brilliant. That works!
Thanks very much for all your help Steve - much appreciated.
Regards,
craig
Thanks Kiran - I looked at the file but if I select an Employee in addition to the Current Year then the results only related to that Employee.