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

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

benvatvandata — Sat, 16 Nov 2024 21:00:03 GMT

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

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

felipe_dutra — Wed, 24 Apr 2019 02:04:35 GMT

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

Anil_Babu_Samineni — Wed, 24 Apr 2019 04:28:09 GMT

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

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

PrashantSangle — Wed, 24 Apr 2019 04:38:58 GMT

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?

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

benvatvandata — Wed, 24 Apr 2019 14:28:20 GMT

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

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

benvatvandata — Wed, 24 Apr 2019 14:34:28 GMT

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