Use field value in expression, without having it as a dimension

[ken_geren]

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))

[Anonymous]

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?

[ken_geren]

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#.

[Anonymous]

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

)

[ken_geren]

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),

[ken_geren · Accepted solution]

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)))