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

Issue with storing date as a variable to be used in set analysis

I have created a variable to store the prior month that user selects.

vPriorMonth =  Date(addmonths(MonthStart(SnapDate),-1),'MMM-YY')

Purpose is to show the previous month's balance if a month is selected.

eg. User select SnapDate = Jul-16

The following expression will generate these outputs

$(vPriorMonth)  => Jun-16

Sum({$<SnapDate=, SnapDate={'Jun-16'}>}MonthEndBalance) => The correct balance if I hardcoded it

Sum({<SnapDate=, SnapDate={'$(vPriorMonth)'}>}MonthEndBalance)  => No results

Sum({<SnapDate=, SnapDate={$(=vPriorMonth)}>}MonthEndBalance)  =>  => No results

I have narrowed it down to the issue is that it is a date field as I have replaced the date check with a unrelated check and the code works fine.

Is there any way to get the set analysis to work?

Additional info, not too sure if it helps.

The date format in the load editor:

SET DateFormat='DDMMMYYYY';

How SnapDate is loaded:

Date(MonthStart(SNAP_DATE),'MMM-YY') AS SnapDate

10 Replies
sunny_talwar

How about this:

Sum({<SnapDate=, SnapDate={"$(=vPriorMonth)"}>}MonthEndBalance)

Not applicable
Author

That doesn't return any result as well.

rahulpawarb
Specialist III
Specialist III

Hello Melvin,

Could you please use below given sample code snippet:

//Definition of vPriorMonth

Date(addmonths(MonthStart(Max(SnapDate)),-1),'MMM-YY')

//Expression to calculate Previous Month Balance

Sum({<SnapDate=, SnapDate={"=$(vPriorMonth)"}>}MonthEndBalance)

Hope this will help!

Regards!

Rahul

sunny_talwar

How about if you remove the first SnapDate... I should have seen this before and removed that...

Sum({<SnapDate={"$(=vPriorMonth)"}>}MonthEndBalance)

Not applicable
Author

//Expression to calculate Previous Month Balance 

Sum({<SnapDate=, SnapDate={"=$(vPriorMonth)"}>}MonthEndBalance)


This gives the current month's balance instead of prior months balance. Could it be that the expression is not evaluated?

Not applicable
Author

Sum({<SnapDate={"$(=vPriorMonth)"}>}MonthEndBalance)


After removing the first SnapDate, expression is still returning 0.

rahulpawarb
Specialist III
Specialist III

Hello Melvin,

Could you please share the application with sample data? This will help us to do the root cause analysis.

Regards!

Rahul

sunny_talwar

Would you be able to share a sample to look at this?

Not applicable
Author

I have attached a sample to the original post.