Question on Qlik Listbox

[Anonymous]

I am new to Qlikview. I have the following requirement.

I have a list box with the release months - Jan 2017, Feb 2017, March 2017, ..... Dec 2017, Jan 2018, Feb 2018... April 2018.

I have expanded this list box to show values horizontally in my dashboard.

To avoid all values to show in the top(the values will keep increasing every year), i need to do the following:

Need to add Year field. Based on user selection of the year, i should show only those in the list box.

Eg: if they select 2017, then Release months should show only from Jan 2017 to Dec 2017 with Jan defualted. When they click 2018, then Jan 2018 - April 2018 with Current Month defualted should show up. Similarly for any other years.

Thanks in advance.

[sunny_talwar]

May be this

Aggr(Only({1<Year = $::Year>}Months), Months)

[shiveshsingh]

It will work..nice

[Anonymous]

Thank you Sunny. With the code you provided, it is highlighting the months related to that year which is good.

How do i make it show only the months corresponding to that year and the other months should be removed completely from the list box (should not be greyed out which happens when i select HIDE EXCLUDED). Also for any previous years, it should default to first month (Jan) and for current year it should default to current month.

Eg:

On selecting 2017, List box should show only Jan 2017 to Dec 2017 with Jan 2017 defaulted.

On selecting 2018, list box should show only Jan 2018 to April 2018 (based on the data) with April 2018 Defaulted.

please let me know if that can be done.

Thanks

[juraj_misina]

Hi Ashwin

I'd define a drill-down group with Year and Month fields and use that one in the list box. Initially it would display years, once a user selects a year it would switch to display corresponding months.

Hope this helps.

Juraj

EDIT: I'm not sure about the "default months" requirement. Maybe when creating the group you could use Year and second dimension would be calculated using Aggr() and its structured parameter to do the sorting:

https://help.qlik.com/en-US/sense/February2018/Subsystems/Hub/Content/ChartFunctions/aggr.htm

I have no way of testing now, I only have QV11.2 and this is available since QV12.

[sunny_talwar]

That is exactly what the expression should be doing. Is it not?

[Anonymous]

Ashwin,

I've done it in this attached QVW - I guess this is what you're trying to achieve.

I have used the same YearMonth field in two separate listboxes using expressions - and applied Hide excluded. This method - it shows only the available months and hides the others(not greying out).

Please see if this is useful.

PS: If you need another workaround, a better way yet would be to bring in separate Year and Month fields in your load script itself.

[Anonymous]

Hi Benazir, Thank you. I think you missed the attachment.

[Anonymous]

The expression highlights the months corresponding to that year but it is not hiding the other months or defaulting.

[Anonymous]

I actually have attached it... dunno why you can't see it... Will try again.

else, try this link: HiDrive