Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Is it possible to limit the rows based on limits on expressions? For example, I have sum(employees), sum(income), sum(tax) in my expressions and I want only those records to be displayed in my table where:
1. sum(employees) > 200 and sum(employees) < 500
2. sum(income) > $500 000 and sum(income) < $1000000
3. sum(tax) > $10 000
Is it possible to display only those records that fulfills upper and lower limits to above expressions ?
Thanks,
Sheetal
Hi,
Try like this
Dimension: AccountType, ID
Expression: = Sum(Aggr(If(sum(Income)>1200, Sum(Income)), AccountType))
Hope this helps you.
Regards,
Jagan.
Try this:
Sum({<Employee = {">=200 <= 500"} >} [Employee])
Sum({<Income = {">= 50000 <= 1000000"}>}[Income])
Sum({<Tax = {">=10000"}>}[Tax])
try
sum({<employees = {">=200<=500"}>}employees)
Hi Don,
Thank you for your reply. However, I tried the below in the expression but it does not give any output. It shows 0 for Number of Employees.
sum({<[EMPLOYEES] = {">=200 <= 500"} >}[EMPLOYEES])
Thanks,
Sheetal.
maybe you leave out these [ ] ??
If your three conditions should apply to all three expressions try this:
Variable:
Expressions:
If each condition should apply only to one expression try these three expressions:
Tried even that below, it doesn't work.
sum({<EMPLOYEES = {">=200 <= 500"} >} EMPLOYEES )
Thanks Gysbert for your reply. I tried for one of the condition. Added a variable called vEmpLimits with value =sum(EMPLOYEES) > 200 and sum(EMPLOYEES) < 500 and modified the expression to if( $(vEmpLimits) , sum(EMPLOYEES) ) but it does not show any value for that field in the table. Can you help?
Hi Gysbert ,
I modified my variable to have only part of the condition sum(EMPLOYEES) > 200. It shows values now wherever the Sum(Employees) is greater than 200. However, remaining rows not having sum(EMPLOYEES) > 200 are also displayed. This is similar to if condition applied in expression. But, I want to display only those rows where sum(EMPLOYEES) > 200
Is this possible ?
Hi Sheetal,
Requirement 1: You need to limit your expressions.
Solution: The one given by Gysbert and others covers it all.
Requirement 2: You need to display only those rows where conditions are satisfied
Solution: Use a calculated dimension.
Go to properties--> dimension tab of the chart and click 'Add calculated Dimension'.
Use this expression.
=If( Aggr (sum(EMPLOYEES), Your_Dimension_name)>10000, Your_Dimension_name)
Give it suitable name.
Check mark 'Suppress When value is Null' check-box.
You may add additional conditions as per requirement.
Warm Regards,
Kalpesh