topic Adding YearMonth and QuarterMonth into same listbox as filter in QlikView

Adding YearMonth and QuarterMonth into same listbox as filter

Anonymous — Thu, 20 May 2021 17:22:07 GMT

Hi All,

We have one ask where, user wants to see payperiod filter as combination of yearmonth and yearquarter

ex.

PayPeriod

202101

202102
202103

2021Q1

202104

202105

202106

2021Q2

202103 and so on,

and when user select '2021Q1' then he should see data for '202103' same for '2021Q2' he should see for 202106 which means whenever yearquarter get selected then, always show data for last yearmonth of that respective quarter and

when user select yearmonth then it should show data for that respective yearmonth only.

ex. '202101' should show data for '202101' and '202103' should show '202103' .

Any suggestions would be helpful.

TIA!

Re: Adding YearMonth and QuarterMonth into same listbox as filter

Or — Thu, 20 May 2021 17:37:59 GMT

Periods:

Load OriginalDate, Monthname(OriginalDate) as PayPeriod

Resident YourTable;

CONCATENATE

Load OriginalDate, Quartername(OriginalDate) as PayPeriod

Resident YourTable;

By Loading both sets of values, alongside the original dates, into a separate table, you can filter using the same field without having to duplicate or modify the original data.

Re: Adding YearMonth and QuarterMonth into same listbox as filter

Vegar — Thu, 20 May 2021 21:54:38 GMT

I'm making some adjustment to the solution by @Or to better fit your desired output as you described it.

Periods:

Load distinct OriginalDate, Monthname(OriginalDate) as PayPeriod

Resident YourTable;

CONCATENATE (Periods)

Load

OriginalDate, Quartername(OriginalDate) as PayPeriod

Resident Periods

Where PayPeriod = monthname(quarterend(OriginalDate));