topic Re: Exclude dimension records based on calculation in QlikView

Exclude dimension records based on calculation

Tue, 25 Oct 2011 08:16:55 GMT

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

Exclude dimension records based on calculation

sbaldwin — Tue, 25 Oct 2011 09:02:22 GMT

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

Exclude dimension records based on calculation

Tue, 25 Oct 2011 09:37:36 GMT

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

Exclude dimension records based on calculation

sbaldwin — Tue, 25 Oct 2011 09:50:30 GMT

I think i see what you mean, try something like this:

= AGGR( if( count( { 1<CurrentYear={$(=only({$} CurrentYear))}>} TotSals) >5,EmpName ) , EmpName)

Thanks

Steve

Re: Exclude dimension records based on calculation

Tue, 25 Oct 2011 10:21:48 GMT

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

Re: Exclude dimension records based on calculation

sbaldwin — Tue, 25 Oct 2011 10:29:50 GMT

Hi, Try this.

Thanks

Steve

Re: Exclude dimension records based on calculation

Tue, 25 Oct 2011 10:36:07 GMT

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

Re: Exclude dimension records based on calculation

Tue, 25 Oct 2011 10:40:30 GMT

try this out

=if(aggr(count(EmpName),CurrentYear) > 5,EmpName,Null())

Re: Exclude dimension records based on calculation

Tue, 25 Oct 2011 10:52:12 GMT

Brilliant. That works!

Thanks very much for all your help Steve - much appreciated.

Regards,

craig

Re: Exclude dimension records based on calculation

Tue, 25 Oct 2011 10:55:42 GMT

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.

Re: Exclude dimension records based on calculation

Tue, 25 Oct 2011 10:57:43 GMT

Thanks Blessy - this too filters records if an Employee is selected in addition to the Current Year.