Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
marlonsoaresQS
Contributor II
Contributor II

Create Expression for KPI

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

marlonsoaresQS_1-1653423170167.png

KPI:  backlog

  marlonsoaresQS_1-1653419844724.png

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.

marlonsoaresQS_0-1653423149625.png

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.

 

 

 

Labels (5)
1 Solution

Accepted Solutions
Fernando_Fabregas
Creator II
Creator II

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)

View solution in original post

8 Replies
Fernando_Fabregas
Creator II
Creator II

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
Contributor II
Contributor II
Author

Hi, Fernando!

how was it:

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

but not working, error in expression:

marlonsoaresQS_0-1653484087890.png

 

 

edwin
Master II
Master II

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
Creator II
Creator II

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

marlonsoaresQS
Contributor II
Contributor II
Author

Hi Edwin!

I adjusted the date format in the script command SET:

marlonsoaresQS_1-1653487885342.png

corrected date format:

marlonsoaresQS_4-1653488459416.png

values of MesAnoBacklog:

marlonsoaresQS_3-1653488016667.png

 

 

 

 

 

marlonsoaresQS
Contributor II
Contributor II
Author

Hi Fernando!

Formula of measure MesAnoBacklog:

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

Fernando_Fabregas
Creator II
Creator II

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
Contributor II
Contributor II
Author

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!