topic Re: Use field value in expression, without having it as a dimension in QlikView

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

ken_geren — Thu, 23 Apr 2015 20:14:34 GMT

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

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

Anonymous — Thu, 23 Apr 2015 21:19:00 GMT

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?

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

ken_geren — Fri, 24 Apr 2015 00:36:43 GMT

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

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

Anonymous — Fri, 24 Apr 2015 13:36:26 GMT

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

)

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

ken_geren — Fri, 24 Apr 2015 13:54:05 GMT

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

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

ken_geren — Sat, 25 Apr 2015 12:23:20 GMT

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