Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys, I hope you can help me out with this one:
I want to make a set analysis with a variable where Dim2 is null, but my code doesn't give me the correct results:
sum(aggr(ONLY({<[Dim1]={'A', 'B', 'C', 'D'}, [Dim2] = {''} >} aggr($(v_1), [Dim1], [Dim2])) / 1000, [Dim1], [Dim2]))
check whether this is working?
sum(aggr(ONLY({<[Dim1]={'A', 'B', 'C', 'D'}>} aggr($(v_1), [Dim1], [Dim2])) / 1000, [Dim1], [Dim2]))
It it is working for you, It should work
sum(aggr(ONLY({<[Dim1]={'A', 'B', 'C', 'D'}, IsNull([Dim2]) = {0} >} aggr($(v_1), [Dim1], [Dim2])) / 1000, [Dim1], [Dim2]))
This code works, but it doesn't give me all the variables I need:
v_New = ONLY({<[Dim1]={'A', 'B', 'C', 'D'}, [Dim2] = {''} >} aggr($(v_1), [Dim1], [Dim2])) / 1000
In the expression:
sum(aggr($(v_New), [Dim1], [Dim3]))
When I change my variable code to yours, it gives me a red line beneath the {0} and no values
AFAIK this [Dim2] = {''} won't query NULL else if the field-value is empty which could be considered as a kind of NULL but it's not the same and not NULL for qlikview. Instead of this you could try something like this: [Dim2] -= {'*'}. Another approach could be to replace NULL with "real" values maybe with something like:
if(len(trim([Dim2])) = 0, '#NV', [Dim2]) as [Dim2]
More could you find here: NULL handling in QlikView.
- Marcus