8 Replies Latest reply: Feb 18, 2016 2:18 AM by Tresesco B

# Variable within an expression

Hi there,

I am trying to display the comparison of sales revenue from the current month and the previous month. I am using the below expressions :

Current Month ------- sum({<vInvoicedate={\$(=(max(vInvoicedate)))}>}\$(=Inv_Ref))

The Above Expressions don't work. But if I replace \$(=Inv_Ref) to a field like ----- sum({\$<vInvoicedate{\$(=monthname(addmonths(Today()),-1)))}>}amount_total) this works just fine but i cant use a field name there because sale revenue calculation is stored in a variable and that variable is Inv_Ref. The calculation of this variable is like this Inv_Ref =  \$(Inv_total) - \$(Ref_Total).

Could someone help me in correcting this expression?

Note: I am a total beginner and novice in Qlik Sense.

Thanks very much

Swetha

• ###### Re: Variable within an expression

How are your other variables ( Inv_total and Ref_Total) defined? If they are defined to calculate a numeric value, your final expression with set analysis would always return the same resultant value irrespective of the set expression you use in the final expression. That is because, expression with set analysis would always expect a Field Name rather than an amount.

• ###### Re: Variable within an expression

Hi tresesco,

Inv_total = Sum(if(wildmatch([number],'*INV*')=1 and mixmatch([state],'Paid')=1,[amount_total],0))

Ref_total = Sum(if(wildmatch([number],'*INV*')=1 and mixmatch([state],'Paid')=1,[amount_total],0))

Inv_Ref is basically calculating Invoice amount minus the Refunds.

Thanks for the quick reponse.

Thanks

Swetha

• ###### Re: Variable within an expression

Well, I guess (since you say that you are a beginner) there would be many a things that have to be looked into your app/qvw. Could you share a sample qvw and explain the expected output against the sample data set? Check this post for posting a sample qvw.:Preparing examples for Upload - Reduction and Data Scrambling

• ###### Re: Variable within an expression

Hi Tresesco,

I am trying to achieve a table which will look like this -

 Currency Jan'16 Month Jan'15 Month Variance % CHF 50,000 675,195 -92.59 CNY 200,000 30,494 555.86 EUR 901,117 980,082 -8.06 USD 207,678 213,729 -2.83 INR 716,347 2,000,000 -64.18

The sales value is invoice minus the refunds hence i had to use variables to calculate that since it is not a straight forward field. To identify invoices and refunds we have to look for strings INV and REF respectively in the invoice number.

Unfortunately I cant share the QVF file, i have sample data in the excel.

How best can i do this calculation without using variables. Please note  i am using Qlik Sense desktop.

Many Thanks

Swetha

• ###### Re: Variable within an expression

Try like:

= Sum({<[Invoice Number]={'*INV*'},State={'Paid'}, [Invoice Date]={'>=\$(=MonthStart(Today()))<=\$(=MonthEnd(Today()))'}>}AmountField)

-

= Sum({<[Invoice Number]={'*REF*'},State={'Paid'}, [Invoice Date]={'>=\$(=MonthStart(Today()))<=\$(=MonthEnd(Today()))'}>}AmountField)

For Previous month, try with:

[Invoice Date]={'>=\$(=MonthStart(Today(),-1))<=\$(=MonthEnd(Today(),-1))'}

• ###### Re: Variable within an expression

That worked like a charm... Thanks very much.

Thanks

Swetha

• ###### Re: Variable within an expression

Nice to know.

• ###### Re: Variable within an expression

Hi Swetha,

Also,

Making use of a master calendar will simplyfy the expression:

use InMonth (timestamp, base_date, period_no)

this will set a flag and once done, you can use this to write simple set analysis expressions.

Thanks,

Sangram.