# App Development

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for
Did you mean:
Contributor

## Help to build a KPI

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.

Regards

4 Replies
Partner

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

Work smarter, not harder
Contributor
Author

Hi Jordy,

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:
rowno() as Mes,
'T' & Ceil (rowno()/3) as Trimestre
AUTOGENERATE (12);

Temp:
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:
\$(varFechaMin) + Iterno()-1 As Num,
Date(\$(varFechaMin) + IterNo() - 1) as TempDate
AutoGenerate 1 While \$(varFechaMin) + IterNo() -1 <= \$(varFechaMax);

MasterCalendar:
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,
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

How should I to add the indicators that I need?

Thank you again.

Partner

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

Work smarter, not harder
Contributor
Author

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 🙂

Tags
Community Browser