how to create last 6 Month in Measure

[Samanehsorournejad]

Hi every one,

I wanted to calculate this KPI:

average hours used per estimated story point.

this KPI have some Conditions as below:
-Select user team = A or B or C or D or DWH (only per team)

- Tickets with the category Optimization, New Request, Change

-Ticket Status: Closed tickets

****Based on data from the last six months*****

I had write the KPI like this:

Avg(Aggr({<KATEGORIE={'Änderung','Neuanforderung','Optimierung'},
LAST_STATUS_NAME={'Geschlossen'},
%Datum= {">=$(=monthstart(max(%Datum),-5))"},
ENTW_TEAM={'Team D','Team A','Team B','Team C','Team Data Warehouse'}
>}
Sum(HOURS) / STORY_POINTS, Ticket_ID))

but it just show me -0.00

but when I write the same KPI whiteout theses %Datum= {">=$(=monthstart(max(%Datum),-5))"},

and use just the graphic filter to filter last 6 Month it shows me the Value: 0,36.

I had attached the Photo.

can anyone please tell me why it show me this Value?

please anyone help me !:)

Thanks in advanced

[HeshamKhja1]

I am thinking two points:

1. Enclose %Datum with square brackets --> [%Datum]. (Not sure though)

2. Make sure the returned value from the expression {">=$(=monthstart(max(%Datum),-5))"} is of the same format of %Datum

[Samanehsorournejad]

thanks for reply, I had done both recommendation that you give me but still I give the wrong Resualt

[Aasir]

A better approach is to rely on QlikView's date filters to achieve the desired result. You can use a calculated dimension to filter the data for the last six months. Modify yours as below:

Avg(Aggr(
{
<KATEGORIE={'Änderung','Neuanforderung','Optimierung'},
LAST_STATUS_NAME={'Geschlossen'},
ENTW_TEAM={'Team D','Team A','Team B','Team C','Team Data Warehouse'},
[Date Filter for Last 6 Months]={'1'} // This is a calculated dimension
>}
Sum(HOURS) / STORY_POINTS, Ticket_ID)
)

Then,
=if(MonthStart(max(%Datum),-5) <= %Datum and MonthEnd(max(%Datum),-5) >= %Datum, 1, 0)

Hope this help you.

[MayilVahanan · Accepted solution]

hi

if you are using nested aggregation, Set analysis need to be apply for both.

Ex: Avg({<ursetanalysis>}Aggr(Sum({<ursetanalysis>}), urdimension))