Get metric value for specific dimension (KPI)

[EugenioAccornero]

Hello, I'm new to Qlik Sense.

I was creating an application where i need to define a KPI that return a value according to the month set as variable (can be changed by user):

e.g.     vMonthAnalysisN = 'Apr'

Before creating the KPI, I've created a table, where for each CLOSE_DATE.Month a specific measure is associated (cumulative one):

(rangesum( above(

(Sum( {<[Phase]={'6'},[Additional Business?]={'si'}>} [Total Price])
+
Sum( {<[Phase]={'4','5'},Status={'Valid'},[Additional Business?]={'si'}>} [Total Price]))/1000

,0,rowno())))

Until here everything is working as expected (I get the expected value for each month).

However if i try to define the KPI in order to return the value for a specific month (the one define as variable) i cannot get the expected one (I get December one); here the formula used for the KPI:

rangesum( above(

Sum( {<[Phase]={'6'},[Close Date.autoCalendar.Month]={'$(=vMonthAnalysis)'},[Additional Business?]={'si'}>} [Total Price])/1000

,0,rowno()))

I've also tried with:

only({<[Close Date.autoCalendar.Month]={'$(=vMonthAnalysis)'}>}

Aggr(

rangesum( above(

Sum( {<[Phase]={'6'},[Additional Business?]={'si'}>} [Total Price])/1000

,0,rowno())),

[Close Date.autoCalendar.Month]))

but no luck...

Thanks for the support!

Eugenio

[jwjackso]

Did you try just 

Sum( {<[Phase]={'6'},[Close Date.autoCalendar.Month]={'$(=vMonthAnalysis)'},[Additional Business?]={'si'}>} [Total Price])/1000

the rangesum is great for a table, but in the KPI you are looking at all the data so I don't think you need the rangesum.

[EugenioAccornero]

Thank you very much for the reply!

If I'm not wrong in that way I'll calculate the sum for the month associated to the variable, correct?

What I'd like to do is to have the cumulative value until that month (for example, if 'vMonthAnalysis' is April, I'd like to have included in the sum all months from January to April included);

Maybe using " <=  " ? it can work?

Or there is another way?

Thanks again!

Eugenio

[jwjackso · Accepted solution]

The "<=" would give you a cumulative amount in the KPI.

Sum( {<[Phase]={'6'},[Close Date.autoCalendar.Month]={'<=$(=vMonthAnalysis)'},[Additional Business?]={'si'}>} [Total Price])/1000

[EugenioAccornero]

Thanks it worked after transforming to single month number!

 {<[Close Month] = {"$(='<=' & Date(vDateAnalysis, 'M'))"}>} , where [Close Month] is a the month number between 1 and 12.

with the 'MMM' format was not working...

Thanks for the support!