topic Use variable in expression in QlikView

Use variable in expression

Thu, 17 Apr 2014 10:28:40 GMT

I have two variables defined

LET vCurrentWeek = Week(Today());

LET vLastWeek = vCurrentWeek-1;

And I have an expression to calculate revenue

=sum({$<Book={"EM EUROBONDS VOL MATCH"}>}IF(PURCHASE_SALE='P',-NOMINAL,NOMINAL)*PRICE/100)

How can I calculate revenue for last week by using my vLastWeek variable and adapting my expression?

Re: Use variable in expression

MK_QSL — Thu, 17 Apr 2014 10:32:06 GMT

=sum({$<Book={"EM EUROBONDS VOL MATCH"}, Week = {"$(=vLastWeek )"}>}IF(PURCHASE_SALE='P',-NOMINAL,NOMINAL)*PRICE/100)

Re: Use variable in expression

Thu, 17 Apr 2014 10:34:14 GMT

I don't have a field called Week

how is that defined?

Re: Use variable in expression

MK_QSL — Thu, 17 Apr 2014 10:35:56 GMT

Write below in script below your DateField

Week(YourDateField) as Week

Re: Use variable in expression

Thu, 17 Apr 2014 10:41:50 GMT

I don't have access to the load script. I'm am doing a binary load from another qvw

Re: Use variable in expression

its_anandrjs — Thu, 17 Apr 2014 10:45:07 GMT

You can try write

Add this script to the expression Week(YourDateFieldHere) = {'$(=Week(Today() )-7 )'

=Sum({$<Book={"EM EUROBONDS VOL MATCH"}, Week(YourDateFieldHere) = {'$(=Week(Today() )-7 )'}>}

IF(PURCHASE_SALE='P',-NOMINAL,NOMINAL)*PRICE/100)

Re: Use variable in expression

Thu, 17 Apr 2014 10:49:29 GMT

I get a blank result when I do that Anand

Re: Use variable in expression

Thu, 17 Apr 2014 10:58:09 GMT

Does my date field need to be in a certain format for that to work?

In my list boxes it appears as DD-MMM-YYYY

Re: Use variable in expression

its_anandrjs — Thu, 17 Apr 2014 11:02:54 GMT

Try like

=sum({$<Book={"EM EUROBONDS VOL MATCH"}, Week(YourDateFieldHere) = {"$(=Week(Today( ))-7 )"}>}IF(PURCHASE_SALE='P',-NOMINAL,NOMINAL)*PRICE/100)

Re: Use variable in expression

Thu, 17 Apr 2014 11:10:52 GMT

It didn't work either. Blank result again

Is it possible my CalendarDate field may not be in the correct format, even though it displays correctly in the list box and tables? Would that make a difference?

Re: Use variable in expression

its_anandrjs — Thu, 17 Apr 2014 11:33:54 GMT

Yes try to convert or may be add week field in the script load.

Re: Use variable in expression

MK_QSL — Thu, 17 Apr 2014 11:40:38 GMT

If you dont have

1) week field or

2) access to script...

you need to use date field...

=SUM({$<Book={'EM EUROBONDS VOL MATCH'},CalendarDate = {'>=$(=WeekStart(Today()-7))<=$(=WeekEnd(Today()-7))'}>}IF(PURCHASE_SALE='P',-NOMINAL,NOMINAL)*PRICE/100)

Re: Use variable in expression

Thu, 17 Apr 2014 11:46:51 GMT

Hi Alex,

Take =max(your_date_field) in text-box .Check your Date format Ex. dd-mm-yyyy ...whatever format it shows simply assign that format using date function like date(Week(Today( ))-7),'Your date format') in expression.

If your field is in text format then you should change text to date by like this...

date(num(date#(your_date_field,'existing date format write here')),'DD-MM-YYYY')

=Sum({$<Book={'EM EUROBONDS VOL MATCH'},

Week(date(num(date#(your_date_field,'existing date format write here')),'DD-MM-YYYY')) = {'$(=Week(Today() )-7)'}>}

IF(PURCHASE_SALE='P',-NOMINAL,NOMINAL)*PRICE/100)

Thanks & regards,

Harshal