How to show specific values in listbox?

[#1]

Hi,

I have dates (last 3 weeks, all month ends and last year end) in listbox.

Dates like:

• 18/8/2015
• 11/8/2015
• 4/8/2015
• 31/7/2015
• 30/6/2015
• 31/5/2015
• another month ends
• 31/12/2014

But I want to show just last 2 weeks, last 2 month ends and last year end:

• 18/8/2015
• 11/8/2015
• 31/7/2015
• 30/6/2015
• 31/12/2014

But sill I have to have all dates loaded in QV report.

I tried to do something like this in listbox expression:

if(match(Datum,max(Datum),MonthEnd(max(Datum)-1)), Datum)

which does not work..

Please, do you have any idea how to do this?

Thanks so much in advance!

[sundarakumar]

Hi Neil,

Something like the attachment should help. Add similar script to create a flag on the dates you have since you want all the data to be present and use the flag on the chart with set analysis to acheieve what you are expecting.

If it is a straight table you can even add the flag as an expression and add supress when value is null and hide it in presentation tab.

for i=41639 to 42004
one:
load date($(i)) as date,
$(i) as date_num
AutoGenerate(1);next i

two:
load max(date) as max_date
Resident one;

let maxdate=peek('max_date',0,'two');
let pastmonthend=floor(num(monthend(addmonths($(maxdate),-1))));
let pastsecondmonth=floor(num(monthend(addmonths($(maxdate),-2))));
let pastyearend=floor(num(YearEnd(AddYears($(maxdate),-1))));
let pastsecondyear=floor(num(YearEnd(AddYears($(maxdate),-2))));

NoConcatenate
three:
load * Resident
one
where date_num>$(maxdate)-14 or match(date_num,$(pastmonthend),$(pastsecondmonth),$(pastyearend),$(pastsecondyear));
drop table one;

Regards,

Sundar

[maxgro]

if you have a calendar add a flag for the dates you want to show in listbox and then use that flag to show the dates in listbox

[maxgro]

another idea in the attachment