Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Partner
Partner

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

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

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])))"}>}

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

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

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)

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

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.
Partner
Partner

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

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

Partner
Partner

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

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).