Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
razvan_brais
Creator III
Creator III

Populate kpis from today with kpis from yesterday

Hey guys.

I have a question , and please help me with some ideas.

I`m importing some info from my database on which I`m creating kpi`s.

The problem is that in my DB some Dates are missing.

So I`m having this :

05/05/2017

06/05/2017

08/05/2017.

For each date I have created some kpi`s (kpiName and kpiNo). In my interface it will look something like this

DateKpiNoKpiNameValue
05/05/20171Test112
05/05/20172Test215
06/05/20171Test113
06/05/20172Test214
08/05/20171Test111
08/05/20172Test217

What I`m trying to do is to add the missing date (07/05/2017) and to put on Value column 0 for each kpi. something like the table below:

DateKpiNoKpiNameValue
05/05/20171Test112
05/05/20172Test215
06/05/20171Test113
06/05/20172Test214
07/05/20171Test10
07/05/20172Test20
08/05/20171Test111
08/05/20172Test217

I`ve achieved to add the missing date by adding this code in Script:

NoConcatenate

MinMax:

Load

ID,

min(HIST_DATE) as MinPeriod,

max(HIST_DATE) as MaxPeriod

Resident IS_acc

Group by

ID;

tempDate:

Load

ID,

date(MinPeriod - 1 + IterNo()) as HIST_DATE

Resident MinMax

while(MinPeriod + IterNo() - 1) <= MaxPeriod;

But the problem is that in my table I can`t see any kpi for 07/05/2017.

Do you have any Idea how can I add the missing rows ?

I can`t post the code here but it will be helpful if you can give me some ideas.

Thank you,

Razvan

2 Replies
razvan_brais
Creator III
Creator III
Author

Nevermind , I`ve resolved.

I forgot to add in MinMax and tempDate the kpiNo and kpiName columns.

Thanks again.

ahaahaaha
Partner - Master
Partner - Master

Hi,

You could just create a Master Calendar, connect it to your data on the date field and use the master calendar fields in the charts. This will allow you to analyze by dates, months, quarters, etc.

Regards,

Andrey