Aging report with Variable and inequality comparison in set analysis

[Anonymous]

Hi all,

I am doing aging report using qlikview

here is the set analysis expression that is working for me

Sum(

  {

<fin_gl_line_trans_datetime={'<$(=today()-30)>=$(=today()-60)'}>

  }

  fin_gl_line_reporting_amt

  )

I want to change today() in above expression to a variable to account for some other factors.

Therefore, I created a variable call todayMinusPaymentTerm with the definition:

today()-(fin_gl_line_trans_datetime+if(IsNull(fin_gl_line_payment_terms_code) or fin_gl_line_payment_terms_code='COD'

  ,

  //true

  0,

  //else

  mid(fin_gl_line_payment_terms_code,1,len(fin_gl_line_payment_terms_code)-1) ) )

where

fin_gl_line_trans_datetime is invoice date, its a date formatted as number

fin_gl_line_payment_terms_code is , sample of data look something like 30D ,COD, 7D, 15D , sometimes it can be blank as well.

Here is where i am stuck, i change the expression to

Sum(

  {

<fin_gl_line_trans_datetime={"<$(=todayMinusPaymentTerm-30)>=$(=todayMinusPaymentTerm-60)"}>

  }

  fin_gl_line_reporting_amt

  )

I do not have any syntax error but the reporting amount become 0. is it a sematic error?

Message was edited by: Tian Kian Tan added qvw

[Anonymous]

Hi,

Errors wont be visible under the double quotes.

Its best to test what you have withing the double quotes by maybe dropping a text box on the designer view and pasting the expressions in there to see if they behave as expected.

First check if your date expression produces the correct output then try a few alternatives by changing the expression in the text object and analysing the output

$(=todayMinusPaymentTerm-30)

OR ($(todayMinusPaymentTerm) - 30) etc.

[jonathandienst · Accepted solution]

I suspect you are looking for a line-by-line evaluation of the variable. In other words, the field values used in the variable exist in a record associated with each value of fin_gl_line_reporting_amt.

If that is the case, then using a set expression will not work as the expression (and the variable) is evaluated once for the table and not line by line. As the variable evaluates to null when evaluated this way, the set expression condition is always false , and therefore you get a 0 result.

You may be better off by adding a COD flag to the transaction table or perform the bucketing using an interval match in the load script. For more specific help, I suggest that you provide a small sample qvw and some representative data in and excel or csv file,

[Anonymous]

Hi Jonathan heres the qvw and xlsx which im working on.

Yes i am indeed looking for a line by line evaluation , i am hoping for a dynamic variable where its recalculated every line, isnt it supposed to work like that?

[Anonymous]

Hi Deran,

You are right, there is no syntax checking if i use quotes.

but using textbox with the following expression yield no result as well

$(=todayMinusPaymentTerm-30)

OR ($(todayMinusPaymentTerm) - 30),

But i tested using expression in the straight table chart $(todayMinusPaymentTerm) works . it just dont work in the set expression

[Anil_Babu_Samineni]

I've changed the variable like this, Because, You have lot of dates with irrespective format. So, Does this values ok for you. If so, Cool or else you may explain more like what is the cause you are adding fin_gl_line_trans_datetime to If Condition

=Today() - (GetSelectedCount(fin_gl_line_trans_datetime) + if(IsNull(fin_gl_line_payment_terms_code) or fin_gl_line_payment_terms_code='COD' or fin_gl_line_payment_terms_code='-' or fin_gl_line_payment_terms_code='              ',

0, mid(fin_gl_line_payment_terms_code,1,len(fin_gl_line_payment_terms_code)-1) ) )

OR

=Today() - (FirstSortedValue(fin_gl_line_trans_datetime,fin_gl_line_trans_datetime) + if(IsNull(fin_gl_line_payment_terms_code) or fin_gl_line_payment_terms_code='COD' or fin_gl_line_payment_terms_code='-' or fin_gl_line_payment_terms_code='              ', 0, mid(fin_gl_line_payment_terms_code,1,len(fin_gl_line_payment_terms_code)-1) ) )

Output seems This:

[Anonymous]

Hi Anil,

I am adding fin_gl_line_trans_datetime to an if condition so as to handle some error in the data in the payment term field. 

The fin gl line trans datetime field is like the invoice date field.  So to get the date counterparts suppose to pay the invoice, I take invoice date plus the payment term date.

The date format was actually in numeric form originally,  I have changed it in the right click properties/number tab.  It can be easily backed to any date format that we need in that tab.

[Anil_Babu_Samineni]

I am not fully understand your intend, May be try this

=Today() - (Only({<fin_gl_line_trans_datetime = {">= $(=Min(fin_gl_line_trans_datetime)) <= $(=Max(fin_gl_line_trans_datetime))"} >}fin_gl_line_trans_datetime) + if(IsNull(fin_gl_line_payment_terms_code) or fin_gl_line_payment_terms_code='COD'

or fin_gl_line_payment_terms_code='-' or fin_gl_line_payment_terms_code='              ', 0, mid (fin_gl_line_payment_terms_code , 1,len(fin_gl_line_payment_terms_code)-1) ) )

OR

=Today() - (Only({<fin_gl_line_trans_datetime = {">= Num(Min(fin_gl_line_trans_datetime)) <= Num(Max(fin_gl_line_trans_datetime))"} >} fin_gl_line_trans_datetime) + if(IsNull(fin_gl_line_payment_terms_code) or fin_gl_line_payment_terms_code='COD'

or fin_gl_line_payment_terms_code='-' or fin_gl_line_payment_terms_code='              ', 0, mid (fin_gl_line_payment_terms_code , 1,len(fin_gl_line_payment_terms_code)-1) ) )

[Anonymous]

Hi Anil,

I realise that it seems impossible for dollar sign expression to evaluate to get a new variable line by line ,

actually i wanted to do something like this Redmond Debt Profile Chart.qvw | Qlik Community

but with a dynamic variable(hoping that it can re-evaluates line by line) in the modifier.