Condition to filter out rows

[former_member128137]

Dear Community,

I'm having the below query:

I'd like to filter out all rows that meet the condition "Balances = 0".

This shall only be applicable to the last three years.

The expected outcome would be that the red marked rows are being filtered out.

I have been testing pretty much all possible combinations in the "Characteristic Assignment", but I cannot seem to achieve it.

Is there any way to do this by means of the Query Designer?

Many thanks for your help!

T

[umashankar_poojar · Accepted solution]

Hi,

Since the year is used for drilled down and condition should apply only for last three years. I don't think it is possible OR I don't the answer. But can you please give a try 'Zero Suppression' Effect On only 'Columns'. Below is the example shown at BEx designer level,

Alternative approach, let's assume users are interested in 4 to 5 years of data. Let's create 5 local selections and include year in each selection. Create one more formula to add last three columns, then apply condition on this formula to display values>0. It should give you result. Keep your formula either 'Hide Can Be shown' OR always hide.

Update if it works, hope it is helps!

Thanks,

Umashankar

[former_member128137 · Accepted solution]

Hi Umashankar,

thanks a lot for your valuable suggestions.

Suppressing the columns unfortunately is not an option, since the users want to see all years.

Your second option is interesting and I just created a query which I will present to the business.

Let's see if they can live with a fixed range.

Best regards,

T

[umashankar_poojar · Accepted solution]

Hi ,

You just ask users, how many years old and future (if any) is needed. Based on that you can choose offset - / + to the filter and get the desired output.

They should accept the approach, anyway they would need the result not format. Because format is only for columns, rows will be same as now.

The pain point here is to create a text variables to show the year in each column dynamically.

Let's see once you have feedback from users, we can think of next steps.

Thanks,

Umashankar

[former_member128137 · Accepted solution]

Thanks Umashankar,

they were happy with pre-defined 10 years and so I was able to work with the offset and apply the filter / condition to the last 3 columns.

The text variable was not an issue: I created one as Replacement Path with Reference Characteristic "Fiscal Year". Since I had the Key Figures' variable based on the Fiscal Year (with the offset), each column has its correct heading.

Thanks again to both of your help!

Best regards,

T

[Loed]

Hi,

Is your condition BALANCES = 0? Perhaps the rows marked in red were not hidden since you have a negative value in 2011 balance..But if you make your condition BALANCE <= 0 then rows marked in red will be hidden..Try it..

Regards,

Loed

[former_member128137]

Hi Loed,

thanks for directing me in the right direction.

However, it's still not quite the outcome I'd expect. The condition applies to the result, but not for values in specific cells.

E.g.:

The original query without condition:

Then applying condition "Balances Not Equal To 0" with Characteristic Assignment "Individual..." checked Currency and Entry Code leads to this, which is fine:

But let's say I now want to exclude the row showing -8498.34 (the yellow marked line from above).

I applied condition "Balances Less Than or Equal To -8498" with Characteristic Assignment "Individual..." and checked Currency, Entry Code and Fiscal Year.

But the row doesn't disappear, the "Condition not applicable".

Is there any chance that I can get a filter on this conjunction, rather than only the result values?

Many thanks!

T

[Loed]

Hi,

So you wanted to hide the CELLS having ZERO in this table? I'm not sure if we can apply a condition based on specific cell..

May I ask what do you really want to achieve? What is your expected output based on your sample?

Regards,

Loed

[former_member128137]

Hey Loed,

I could apply the needed condition as soon as I had all columns available in the Query Designer. At first I kept it flexible, but then I pre-defined ten years and created ten columns with Offset.

Then I was able to apply the conditions to the respective columns, such as the last 3 years.

Many thanks!

T