topic Help to build a KPI in App Development

Help to build a KPI

AntonioSM — Mon, 15 Jul 2019 10:35:28 GMT

Hi everyone,

i'm new in Qlik and i have some problem to create different KPI's

I need to build something like this:

I've tried with differents formulas but it doesn't work properly.

In each KPI, i need the total sales per Week, Month, Quarter and Year with the actual date and the comparison with the same Week, month, quarter and year from last year.

Thank you for your answers 🙂

Regards

Re: Help to build a KPI

JordyWegman — Tue, 16 Jul 2019 09:54:49 GMT

Hi,

If you have a calendar, create some indicators for the current week/month/quarter and year. Then you can use formulas like:

Sum({$< _indCurrentWeek = {1}>}Sales) Sum({$< _indCurrentMonth = {1}>}Sales) Sum({$< _indCurrentQuarter = {1}>}Sales) Sum({$< _indCurrentYear = {1}>}Sales)

And for the previous year:

Sum({$< _indLYWeek = {1}>}Sales) Sum({$< _indLYMonth = {1}>}Sales) Sum({$< _indLYQuarter = {1}>}Sales) Sum({$< _indLYYear = {1}>}Sales)

Jordy

Climber

Re: Help to build a KPI

AntonioSM — Tue, 16 Jul 2019 13:09:48 GMT

Hi Jordy,

Thanks for your answer.

I have a calendar but i don't know how to create the indicators for the current week/month/quarter and year.

My calendar is :

QuartersMap:
MAPPING LOAD
rowno() as Mes,
'T' & Ceil (rowno()/3) as Trimestre
AUTOGENERATE (12);

Temp:
Load
min(FechaVenta) as FechaMin,
max(FechaVenta) as FechaMax
Resident TiendasVentasComprasStock_261;

Let varFechaMin = Num(Peek('FechaMin', 0, 'Temp'));
Let varFechaMax = Num(Peek('FechaMax', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
$(varFechaMin) + Iterno()-1 As Num,
Date($(varFechaMin) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varFechaMin) + IterNo() -1 <= $(varFechaMax);

MasterCalendar:
Load
TempDate AS FechaVenta,
Day(TempDate) As Dia,
week(TempDate) As Semana,
Month(TempDate) As Mes,
Year(TempDate) As Año,
ApplyMap('QuartersMap', month(TempDate), Null()) as Trimestre,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as SemanaAño,
WeekDay(TempDate) as SemanaDia
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

How should I to add the indicators that I need?

Thank you again.

Re: Help to build a KPI

JordyWegman — Tue, 16 Jul 2019 13:21:39 GMT

Hi Antonio,

You should first create a vToday:

let vToday = Today(); let vCY = year($(vToday)); let vCM = num(month($(vToday)));

Then use this in your Master Calendar:

// year if(year(Date)=$(vCY), 1, 0) as _indCY, if(year(Date)=$(vLY), 1, 0) as _indLY, // month if(month(Date)=$(vCM), 1, 0) as _indCM, if(month(Date)=$(vLM), 1, 0) as _indLM, // week if(weekyear(Date) & '-' & right('00' & week(Date),2) = weekyear($(vToday)) & '-' & right('00' & week($(vToday)),2), 1,0) as _indCW, if(weekyear(Date) & '-' & right('00' & week(Date),2) = weekyear($(vToday)-7) & '-' & right('00' & week($(vToday)-7),2), 1,0) as _indLW,

Jordy

Climber

Re: Help to build a KPI

AntonioSM — Wed, 17 Jul 2019 08:46:26 GMT

Hi Jordy,

you are really helping me but i have found some problems doing the indicators.

The script works and load the dates but when i check the MasterCalendar all the indicators are 0.

Do you know why?

Maybe is something easy but i'm learning now how to use QlikSense and at the moment i don't know to solve it.

Thank you again for your help 🙂