Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
bobby_chowdary
New Contributor III

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
MVP
MVP

Re: Display every 14th date

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
robin_hausdoerfer
Valued Contributor III

Re: Display every 14th date

Just create a calculated dimension:

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

and suppress null values...

bobby_chowdary
New Contributor III

Re: Display every 14th date

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
Valued Contributor III

Re: Display every 14th date

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

MVP
MVP

Re: Display every 14th date

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

robin_hausdoerfer
Valued Contributor III

Re: Display every 14th date

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

In list boxes null values won't be displayed.

robin_hausdoerfer
Valued Contributor III

Re: Display every 14th date

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;

bobby_chowdary
New Contributor III

Re: Display every 14th date

After applying the logic of

MVP
MVP

Re: Display every 14th date

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

MVP
MVP

Re: Display every 14th date

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