Function Kpi

[Sergio0820]

I need calculate a function to print the variation (business plans) to 2021 and 2022 but i need compare so sar this month 

I use this function but not work 

If ([Calendario.Mes]<=Month(today(0)),sum({<[Calendario.Año]={$(=Max([Calendario.Año]))}>}[Planescomerciales])
/

If ([Calendario.Mes]<=Month(today(0)),sum({<[Calendario.Año]={$(=Max([Calendario.Año])-1)}>}[Planescomerciales])

[Vishal_Gupta]

Hi @Sergio0820 I would suggest you to simply write YTD set expression instead of IF

This will give you proper YTD vs Previous YTD comparison

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

/

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(AddYears(Max(DateNum),-1))))<=$(=AddYears(Max(DateNum),-1))"}>} Sales)

For more details you may follow this link

[QFabian]

Hi @Sergio0820 , please check if this works for you, it just uses a common set analysis expression,

Script:

Data:
Load * INLINE [
Fecha, Valor
01-01-2021, 453
01-02-2021, 963
01-03-2021, 240
01-04-2021, 548
01-05-2021, 341
01-06-2021, 457
01-07-2021, 181
01-08-2021, 306
01-09-2021, 900
01-10-2021, 273
01-11-2021, 557
01-12-2021, 743
01-01-2022, 571
01-02-2022, 496
01-03-2022, 674
01-04-2022, 681
01-05-2022, 389
01-06-2022, 831
01-07-2022, 354
01-08-2022, 961
01-09-2022, 115
01-10-2022, 796
01-11-2022, 530
];

Data2:
Load
Fecha,
year(Fecha) as Year,
Month(Fecha) as Month,
Valor
Resident Data;
drop table Data;

Expression:

sum(Valor)
/
sum( {$<Year = {$(#=Only(Year)-1)}>} Valor )

As you can see in the picture , you just have to select one Year and one Month

Check about set analysis here:

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAnalys...

You can add max function to not to have to select a month and a year

https://help.qlik.com/es-ES/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAnalys...