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: 
benvatvandata
Partner - Creator II
Partner - Creator II

Converting Current Year & Month Selection Into Date for Set Analysis Filter

Hi,

I have looked around in the community for a solution to this, but can't seem to find one that matches what I need.

My issue is: When a Month is selected and Year NOT selected... I need my expression to filter a specific date field (i.e. [Bill Date]) on the Current Year AND the Month that is selected.

 

For example, if April is selected and no year selection is made, I need my set analysis to do the equivalent of:

[Bill Date] = {"<=$(=date('2019-04-30','MM/DD/YYYY') )>=$(=date('2019-04-01','MM/DD/YYYY'))"}

 

The part I can't figure out is how to do it WITHOUT any triggers, scripting, or variables... Does anyone know how to do this?

 

Thanks,

Ben

 

 

5 Replies
felipe_dutra
Partner - Creator
Partner - Creator

The easiest is you create a Month/Year Bill field in your data model, and use in the set analysis only:

{<[Month/Year Bill]={"$(=MonthName(Max([Month/Year Bill])))"}>}

Anil_Babu_Samineni

Perhaps this way?
If(GetSelectedCount(Month)>0 and GetSelectedCount(Year)=0, Sum({<[Bill Date]={">=Date(MonthStart(Max([Bill Date])),'MM/DD/YYYY'))<=$(=Date(MonthEnd(Max([Bill Date])),'MM/DD/YYYY'))"}>} Measure), Sum(Measure))

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
PrashantSangle

How did you know if no year is selected then for which year it should show data.
Like you suggested,
if April is selected then it should capture data for Apr 2019 not for Apr 2018 or any other year??
Is there any logic behind it?
Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
benvatvandata
Partner - Creator II
Partner - Creator II
Author

I had to modify this slightly, but this actually worked for the scenario I gave! 

However... now I'm struggling with the scenario where a Year is selected, but no month selection... 

 

For example: If 2018 is selected, I want data for April 2018.

 

Thanks in advance!

Ben

benvatvandata
Partner - Creator II
Partner - Creator II
Author

Basically the logic is somewhat a combination of:

- if no month is selected, display data for current month

- if no year is selected, display data for current year

 

The part I'm struggling with now is when a Year is selected, but no Month selected... and let's just use the example where they choose 2018 as the Year, how do I filter to display data for today's month (April), but from the year 2018 (basically data for April 1st 2018 - April 30th 2018).