Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
I've got a General Ledger chart, and I want to hide all accounts where the the "Debits - Credits" sum is zero (not activity on the account). To accomplish that, I've got this statement here
If(SUM( TOTAL YeartoDateTransactionDebitAmount) > 0,
DSCRIPTN,
Null()
)
This is incomplete however, because it doesn't work over the specified dimension. So then I added this, the "<DSCRIPTN>".
If(SUM( TOTAL <DSCRIPTN> YeartoDateTransactionDebitAmount) > 0,
DSCRIPTN,
Null()
)
and now it doesn't work. I'm not sure why. Does anyone know why it does. What I'm trying to say here is "Does the Sum of YearToDateTransactionDebitAmounts come to greater than zero for this DSCRIPTN, if so, show it"
Any help is greatly appreciated.
EDIT: However, this works
if(Aggr(sum(YeartoDateTransactionDebitAmount),DSCRIPTN) > 0,DSCRIPTN)
So by nature, you either need to use TOTAL in aggregation of your calculated dimension (without aggr) or use Aggr() function when you are using Sum, Min, Max, Avg,.... in your dimension. If you use TOTAL with a field name, then you would need to use Aggr() function.
Is this used as a calculated dimension?
What do you mean by calculated dimension? I got AGGR to work like a charm, but I'm not sure why one worked and the other didn't seeing as how they are semantically the same (at least I think).
Sorry, you are using Qlik Sense, so is this a dimension or measure? My guess is that you used this as dimension?
Yep, it's a Dimension.
So by nature, you either need to use TOTAL in aggregation of your calculated dimension (without aggr) or use Aggr() function when you are using Sum, Min, Max, Avg,.... in your dimension. If you use TOTAL with a field name, then you would need to use Aggr() function.