Help to build a KPI

[AntonioSM]

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

[JordyWegman]

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

[AntonioSM]

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.

[JordyWegman]

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

[AntonioSM]

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 🙂