Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have linked my date field with master calendar date field. Now I need to find the missing dates in my data using master calendar date
in an expression.
The expression for actual days is
Count(DISTINCT {<Field name>} DATE) which is giving correct results.
Now I want to find the count of missing dates for each field.
maybe sthing like;
if( aggr(sum(YourMeasure),Date)=0,1,0)
How is Count(DISTINCT {<Field name>} DATE) giving you missing dates? I am not sure I understand....
Count(Distinct {<Field Name>} DATE) is giving all the available dates. But I need the count of missing days in the data.
So, lets say you have a field in your fact table which is always populated called Flag (if you don't have this field, you can create it while loading fact like this 1 as Flag)
and then use this expression
Count(DISTINCT {<FieldName, Flag = {'*'}>} DATE)
The above will give you distinct count of where the fact is available. To get where it isn't available, you can do this
Count(DISTINCT {<FieldName>} DATE) - Count(DISTINCT {<FieldName, Flag = {'*'}>} DATE)
Thanks for the solution. I have another doubt.
If my field has value from January to March, I want to count all dates present between these months as total available days even if values are missing for days.
Can you share a sample?
Create a flag in script while loading date column :
if(len(date)>0,1,0) as dateflag
and then where dateflag is set to 0 is your missing date column.
This is the script to load the files from folders which is working fine. To calculate available dates in my data I'm using this expression.
Count(DISTINCT {<Funds = {'*'}>} DATE).
I also need to calculate the count of missing dates for each field value and the total available dates for them.
Sub DoDir(Root)
for each ext in 'xlsx'
for each File in FileList(Root&'\*.'&ext)
Table:
load
filepath() as path,
mid(SubField(FilePath(),'/',5),1,6) as Funds,
MakeDate(
mid(SubField(FilePath(),'/',6),1,4),
Month(Date#(mid(SubField(FilePath(),'/',7),1,3),'MMM')),
mid(SubField(FileBaseName(),'_',-1),1,2)
) as DATE
from ['$(File)'](ooxml,no labels);
Next File
Next ext
for Each Dir in DirList(Root&'\*')
call DoDir(Dir)
next Dir
end sub
call DoDir('lib://pathto/Funds')
Master:
Load Date(TempDate) as date_cal,TempDate as DATE,
week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day;
Load
date(mindate + IterNo() ) as TempDate,maxdate
while mindate + IterNo() <= maxdate;
Load
min(FieldValue('DATE' , RecNo()))-1 as mindate,
max(FieldValue('DATE' , RecNo())) as maxdate
AutoGenerate FieldValueCount('DATE');
The expression always give 1, unable to find missing dates.