Find missing dates

[qlik_aparna]

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.

[OmarBenSalem]

maybe sthing like;

if( aggr(sum(YourMeasure),Date)=0,1,0)

[sunny_talwar]

How is Count(DISTINCT {<Field name>} DATE) giving you missing dates? I am not sure I understand....

[qlik_aparna]

Count(Distinct {<Field Name>} DATE) is giving all the available dates. But I need the count of  missing days in the data.

[sunny_talwar]

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)

[qlik_aparna]

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.

[sunny_talwar]

Can you share a sample?

[passionate]

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.

[qlik_aparna]

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');

[qlik_aparna]

The expression always give 1, unable to find missing dates.