Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

qlik_aparna
New Contributor III

Find missing dates

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.

Tags (1)
14 Replies
OmarBenSalem
Esteemed Contributor

Re: Find missing dates

maybe sthing like;

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

Re: Find missing dates

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

qlik_aparna
New Contributor III

Re: Find missing dates

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

Re: Find missing dates

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

Re: Find missing dates

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.

Re: Find missing dates

Can you share a sample?

passionate
Valued Contributor

Re: Find missing dates

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

Re: Find missing dates

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

Re: Find missing dates

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

Community Browser