Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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 :

Previous Month ----- sum({$<vInvoicedate={$(=monthname(addmonths(Today()),-1)))}>}$(=Inv_Ref))

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?

Appreciate all your help.

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

Thanks very much

Swetha

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

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



8 Replies
MVP
MVP

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.

Not applicable

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

MVP
MVP

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

reddys310
Honored Contributor II

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.

Not applicable

Re: Variable within an expression

Hi Tresesco,

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

CurrencyJan'16
Month
Jan'15
Month
Variance
%
CHF50,000675,195-92.59
CNY200,00030,494555.86
EUR901,117980,082-8.06
USD207,678213,729-2.83
INR716,3472,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

MVP
MVP

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



Not applicable

Re: Variable within an expression

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

Thanks

Swetha

MVP
MVP

Re: Variable within an expression

Nice to know.