Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Just create a calculated dimension:
=if(mod("Snapshot Date"-min( TOTAL "Snapshot Date"), 14) = 0,"Snapshot Date")
and suppress null values...
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
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
Why do you want to show 4/3/2016 instead of 3/27/16 and 4/10/2016?
You could additionally create a list box with the same expression:
In list boxes null values won't be displayed.
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;
After applying the logic of
The output is
zero....
want to display the 15th date starting from min date of snapshot date
That's not what he suggested. Why have you added ...=Min([Snapshot Date]) at the end?
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;