Previous month deposit amount

[rajnirughwani]

Hi,

I have a report where I wanted calculate ratios based on current month/previous month deposit

I have this condition to check first 

sum( {<stateInt={'0'},txType={'ManChkback'}>}amountAbs_amount)
/
sum( {<stateInt={'0'},txType={'CcDep'}, [created.autoCalendar.YearMonth] = 'addmonths([created.autoCalendar.YearMonth], -1)'>}amountAbs_amount)

This code is not extracting any value

If I just use this code for current month

sum( {<stateInt={'0'},txType={'ManChkback'}>}amountAbs_amount)/sum( {<stateInt={'0'},txType={'CcDep'}>}amountAbs_amount)

It gives me current months deposit but I also want previous month deposit in same report

[JordyWegman]

Hi,

Do you have some data to test with?

The reason for your first formula not working is because you don't use {} for the part:

[created.autoCalendar.YearMonth] = addmonths([created.autoCalendar.YearMonth], -1)

Should be:

[created.autoCalendar.YearMonth] = {"=addmonths([created.autoCalendar.YearMonth], -1)"}

It could be that you would have to add your date formula.

sum( {<stateInt={'0'},txType={'ManChkback'}, [created.autoCalendar.YearMonth] = {"=addmonths([created.autoCalendar.YearMonth], -1)"}>}amountAbs_amount)

/

sum( {<stateInt={'0'},txType={'CcDep'}, [created.autoCalendar.YearMonth] = {"=addmonths([created.autoCalendar.YearMonth], -1)"}>}amountAbs_amount)

Jordy

Climber

[rajnirughwani]

Hi JordyWegman,

Thanks for your reply on my post. 

Here is the data if you can help with this. Would really appreciate 

Thanks 

Please help me with this calculation. 

Thanks a lot in advance

Here I have 3 tables.

I want to calculate ratio based on table 1/2

I want values of Feb-21 of table 1/Jan-21 of table 2 (This is basically getting values of current month from table 1 / previous month value from table 2)

But somehow I can't get the values of previous month so the ratio calculation is incorrect.

Can someone pls help me with Set analysis statement

I am using this for DC count/DP count

if(Card = 'VP', Count(if(stateInt ='0' and txType ='MChargebck' and [chargebckdate.autoCalendar.Month], ChargebckID))
/
Count({<stateInt={'0'},txType={'CDeposit'},created_month = ${"=addmonths([created.autoCalendar.YearMonth], -1)"}>}originTxId),

if(Card = 'Mcard', Count(if(stateInt ='0' and txType ='MChargebck' and [chargebckdate.autoCalendar.Month], ChargebckID))
/
Count({<stateInt={'0'},txType={'CDeposit'},created_month = ${"=addmonths([created.autoCalendar.YearMonth], -1)"}>}originTxId)))

I also have previous month calculation in my load script

created_month = $ previous_month but this one really doesn't help me much

[chrismarlow · Accepted solution]

Hi,

Have you looked at Above() function?

Above - chart function ‒ Qlik Sense on Windows

Cheers,

Chris.

[rajnirughwani]

Hi Chrismarlow,

I dint know about this function. Just tried and it worked for me like a magic. Thanks a lot 🙂