Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am using a chart pivot table and need to create an expression to create account balances, however certain records should be excluded based on a field value. The field name is APP, it is in the fact table but not used on the pivot table. The expression results are only correct if the field is added to the pivot table. Is this a restriction with QlikView? If not, how do I get the field value in the expression?
Here is the expression.
If(
[APP]='_B',
sum({$<[Location] -= {'790','791','793','794','795','796','797','798','799'},[APP] -= {'CE'}, [Month #]={"$(=min(Month))"} >} AMDOL),
sum({$<[Location] -= {'790','791','793','794','795','796','797','798','799'},[APP] -= {'CE'} >}AMDOL))
Found to solutions to this problem
Solution 1
sum({$<[Location] -=
{'790','791','793','794','795','796','797','798','799'},[APP] -= {'CE','_B'}
>} AMDOL)
+sum({$<[Location] -= {'790','791','793','794','795','796','797','798','799'},[APP] = {'_B'}, [Month #]={"$(=min(Month))"} >} AMDOL)
or Solution 2
sum({$<[Location] -= {'790','791','793','794','795','796','797','798','799'},[APP] -= {'CE'} >} if([APP]='_B' AND [Month #]=$(=min(Month)),AMDOL,IF([APP]<>'_B',AMDOL,0)))
Ken, when you do this
If(
[APP]='_B',
and APP is not a dimension, QlikView will actually apply aggregate function ONLY so it will actually be
If(
ONLY( [APP] ) ='_B',
since this is not the case with you data and because ONLY returns only one value and null otherwise, your expression would not work properly.
is the only different between two expressions that one is for the oldest month while the other one is not?
The application is displaying account balances. The fact table has beginning account balances for each month ( APP = "_B"). If multiple months are selected then only the beginning balance for the first month should be used. The expression shown is adding up the beginning balance for each month. (Wish I could find a bank that does that.)
Is there a way to add the APP field as a dimension but not have to displayed or summarized by that field? When I added this field as a dimension then I got totals by account#/APP. The totals must only be by account#.
use as dimension but not add as dimension - no, it does not really make sense and does not work like that.
But if I get it, you can just do something like this as your expression:
if ( GetSelectedCount(Month) = 1,
expression1,
expression2
)
Here's a sample of the data. If month 1 and 2 are selected then the dollar amount (AMDOL) for the 2nd '_B' record should be excluded from the totals. So the SUM() function needs to exclude the set of records where [APP]='_B' and [Month #] > min(Month).
something like this
sum({$<[Location] -= {'790','791','793','794','795','796','797','798','799'},[APP] -= {'CE'},
(exlude this set ([APP] = ‘_B’ AND [Month#] > min(Month) ) >} AMDOL),
Found to solutions to this problem
Solution 1
sum({$<[Location] -=
{'790','791','793','794','795','796','797','798','799'},[APP] -= {'CE','_B'}
>} AMDOL)
+sum({$<[Location] -= {'790','791','793','794','795','796','797','798','799'},[APP] = {'_B'}, [Month #]={"$(=min(Month))"} >} AMDOL)
or Solution 2
sum({$<[Location] -= {'790','791','793','794','795','796','797','798','799'},[APP] -= {'CE'} >} if([APP]='_B' AND [Month #]=$(=min(Month)),AMDOL,IF([APP]<>'_B',AMDOL,0)))