Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Creator III
Creator III

Re: Populate kpis from today with kpis from yesterday

Nevermind , I`ve resolved.

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

Thanks again.

Highlighted
Master
Master

Re: Populate kpis from today with kpis from yesterday

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