Create Expression for KPI

[marlonsoaresQS]

Hello community!

I need help with Qlik Sense, I'm not getting to evolve in creating a KPI. Where in this panel I need to show information on the total number of calls in the backlog (open) for the month and the calls that were open from the previous month (if any, of course). The selection of the initial month occurs via a filter, being month/year.

Filter: month/year

KPI:  backlog

  

In my database, I have the dimension "month/year" and measure "open/backlog", summarizing the graph shows this information month by month, however, I would like to show only the backlog of the selected month and the previous month in this KPI what do i need to do.

Eg: if you select the month of Feb/2022, the panel would show the information of a total of 5 calls in the backlog (Feb/2022+Jan2022).

Is this possible in Qlik?

Thank you in advance.

[Fernando_Fabregas]

Hi! Try to add this set analysis in your measure:

{<YourDate={">=$(=MonthStart(Max(YourDate),-1))<$(=MonthEnd(Max(YourDate)))"}>}

The date field must be the same of your filter pane.

This set analysis will clear your previous filter only inside your measure.

Regards, Fernando 

[marlonsoaresQS]

Hi, Fernando!

how was it:

{<MesAnoBacklog={">=$(=MonthStart(Max(MesAnoBacklog),-1))<$(=MonthEnd(Max(MesAnoBacklog)))"}>}

but not working, error in expression:

[edwin]

the expression should be correct assuming your dimension is date with the correct format.  can you chech the values of MesAnoBacklog?  maybe post sample data.

[Fernando_Fabregas]

Please send the complete formula of your measure, so we can check it better...

[marlonsoaresQS]

Hi Edwin!

I adjusted the date format in the script command SET:

corrected date format:

values of MesAnoBacklog:

[marlonsoaresQS]

Hi Fernando!

Formula of measure MesAnoBacklog:

Date(MonthName(Date#("Dia do calendário",'DD.MM.YYY')),'MMM/YYYY') as MesAnoBacklog,

[Fernando_Fabregas · Accepted solution]

Hi Marlon. 

You are getting the error "Garbage after expression..." in the expression of your KPI Backlog, not in the script. 

You must have an expression like this in your KPI:

Count( {<MesAnoBacklog={">=$(=MonthStart(Max(MesAnoBacklog),-1))<$(=MonthEnd(Max(MesAnoBacklog)))"}>} MesAnoBacklog)

[marlonsoaresQS]

Hi Fernando.

it worked like this for me:

count({<MesAnoBacklog={">=$(=MonthStart(Max(MesAnoBacklog),-1)) <=$(=MonthEnd(Max(MesAnoBacklog)))"}>}
if(Etapa=1 and IsNull([Data de Fechamento]), [Em aberto (Backlog)]))

thank you very much for the support!