Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
tresesco
MVP
MVP

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



View solution in original post

8 Replies
tresesco
MVP
MVP

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
Author

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

tresesco
MVP
MVP

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

reddy-s
Master II
Master II

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
Author

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

tresesco
MVP
MVP

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
Author

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

Thanks

Swetha

tresesco
MVP
MVP

Nice to know.