Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
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
Not applicable

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

How about this:

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

Not applicable

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

That doesn't return any result as well.

rahulpawarb
Not applicable

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

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
Not applicable

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

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

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

Not applicable

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

//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

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

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


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

rahulpawarb
Not applicable

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

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
Not applicable

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

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

Not applicable

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

I have attached a sample to the original post.