Variable within an expression

[#1]

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

[tresesco]

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.

[#3]

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]

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]

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.

[#6]

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

[tresesco · Accepted solution]

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]

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

Thanks

Swetha

[tresesco]

Nice to know.