Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to filter on a dollar amount.
This does return records: =Aggr(if((SUM(BUDGET_AMT) > 1), MasterKey),MasterKey)
This does NOT return any records. =Aggr(if((SUM(BUDGET_AMT) > 1,000), MasterKey),MasterKey)
I have hundreds of records that are over 1,000.
Looking for suggestions
There will be later but right now i just have my MasterKey, The ProjectID and the SUM(Budget)
In PL SQL I got it to work.
group by BUSINESS_UNIT, PROJECT_ID
having to_number(TO_CHAR(sum(BUDGET_AMT), '9999999999')) > 3000000
This makes me think it is a number format issue.
I tried: =Aggr(if(num(SUM(BUDGET_AMT),'99999999999') > 3000000, MasterKey),MasterKey,PROJECT_ID,BUSINESS_UNIT)
No luck
I got it working but not the way I had hoped. For an unknown reason my global MasterKey filter will not work.
I use this global key in many of my sheets but it compares/looks at strings. Why it does not work with numbers is a mystery to me. =Aggr( IF(Sum(BUDGET_AMT)>=3000000 ,MasterKey),MasterKey,REQUEST_ID,PROJECT_ID,ACTIVITY_ID,ACTIVITY_ID_DESCR)
However, by taking suggestions I was able to add logic to all my columns to get the results we were looking for.
My Work Around.
Measures: =IF(Sum(BUDGET_AMT)>3000000, FieldName)
Dimensions: =AGGR(IF(Sum(BUDGET_AMT)>3000000, REQUEST_ID ),FieldName)