topic Re: Display every 14th date in QlikView

Display every 14th date

Anonymous — Sat, 16 Apr 2016 14:53:13 GMT

Hi guys,

I have a date field which contain number of dates. starting from the min date to max date count every 14th date from min date .

I need these in a separate column.

For eg: These are the dates with date column.

I need only Dates which are selected in the image

Re: Display every 14th date

Anonymous — Sat, 16 Apr 2016 15:34:38 GMT

Just create a calculated dimension:

=if(mod("Snapshot Date"-min( TOTAL "Snapshot Date"), 14) = 0,"Snapshot Date")

and suppress null values...

Re: Display every 14th date

Anonymous — Sat, 16 Apr 2016 17:06:42 GMT

I dont want to suppress value. I want to do this in script. Just need a field to use in front end with filtered dates

Re: Display every 14th date

kkkumar82 — Sat, 16 Apr 2016 17:11:36 GMT

If what Robin suggested works , it will show in the list box your required data, the suppress option is for not showing a null or '-' value in the listbox thats all.

Kiran Kumar

Re: Display every 14th date

swuehl — Sat, 16 Apr 2016 17:17:40 GMT

Why do you want to show 4/3/2016 instead of 3/27/16 and 4/10/2016?

Re: Display every 14th date

Anonymous — Sat, 16 Apr 2016 17:51:47 GMT

You could additionally create a list box with the same expression:

In list boxes null values won't be displayed.

Re: Display every 14th date

Anonymous — Sat, 16 Apr 2016 18:08:21 GMT

Excel:

LOAD Date

FROM

[...]

(ooxml, embedded labels, table is Tab);

NoConcatenate

ExcelMin:

LOAD

min(Date) as MINDATE

Resident

Excel;

LET MINDATE = Peek('MINDATE',0,'ExcelMin');

DROP Table ExcelMin;

New:

LOAD

Date,

if(mod(Date-$(MINDATE), 14) = 0,Date) as Date_filtered

Resident Excel;

DROP Table Excel;

Re: Display every 14th date

Anonymous — Sat, 16 Apr 2016 18:24:09 GMT

After applying the logic of

roharoha

The output is

zero....

want to display the 15th date starting from min date of snapshot date

Re: Display every 14th date

swuehl — Sat, 16 Apr 2016 18:34:38 GMT

That's not what he suggested. Why have you added ...=Min([Snapshot Date]) at the end?

Re: Display every 14th date

swuehl — Sat, 16 Apr 2016 21:38:15 GMT

Here is a script that produces the same set of dates:


Dates:
LOAD Date(today()-Recno()+1) as Date
AutoGenerate 300;

TMP:
LOAD Min(Date) as MinDate
Resident Dates;

Let vMinDate = Peek('MinDate',0,'TMP');

DROP TABLE TMP;

LOAD Date, 1 as Flag14
Resident Dates
WHERE Mod(Date- $(vMinDate),14)=0;

Re: Display every 14th date

Anonymous — Sun, 17 Apr 2016 05:14:06 GMT

Thanks Robin Hausdörfer

it working or me

hi swuehl,

Yes your are saying correct

...=Min([Snapshot Date]) at the end?--this thing i am not oberving properly now i correct that

thanks swuehl for your active reply

yes your QVW is maching my exact requirement first of all i need backed column like u created inQVW script

Re: Display every 14th date

Anonymous — Sun, 17 Apr 2016 15:15:11 GMT

I'd just like to point out that the expression I suggested only works, if the HH:MM:SS value is exactly the same.

If not you should apply the floor() function to all timestamps.