topic Re: How to show specific values in listbox? in QlikView

How to show specific values in listbox?

Wed, 19 Aug 2015 14:56:45 GMT

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!

Re: How to show specific values in listbox?

sundarakumar — Wed, 19 Aug 2015 15:31:01 GMT

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

Re: How to show specific values in listbox?

maxgro — Wed, 19 Aug 2015 15:34:37 GMT

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

Re: How to show specific values in listbox?

maxgro — Wed, 19 Aug 2015 15:42:43 GMT

another idea in the attachment