Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Display every 14th date

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.

Capture.PNG

I need only Dates which are selected in the image2.PNG

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

11 Replies
Anonymous
Not applicable
Author

Just create a calculated dimension:

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

and suppress null values...

Anonymous
Not applicable
Author

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

kkkumar82
Specialist III
Specialist III

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

swuehl
MVP
MVP

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

Anonymous
Not applicable
Author

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

In list boxes null values won't be displayed.

Anonymous
Not applicable
Author

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;

Anonymous
Not applicable
Author

After applying the logic of

swuehl
MVP
MVP

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

swuehl
MVP
MVP

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;