using yearly average for KPI

[WhatsUp]

Hi guys, 

I need to calculate the churn by the formula :

# customers churn / average churn of last year. 

I want to show the KPI as lineplot for each month. The x-Axis is the year-month value. For the y-axis I got the following set expression:

Aggr(count(distinct churnedCustomer), Year-Month_Churn)
/

Aggr(count(distinct churnedCustomer), Year_Churn)/num(month(now()))

I think the result works fine, but for the average, I only get one value. This means in the line chart there is only one value. Not for each month. Since the average value is based on the year it is supposed to be one value, but I still need to display it for each month, such that I can calculate the KPI for each month.

Can someone help me please?

[vincent_ardiet_]

Your second Aggr is returning only one value, QS tries to allocate it in your chart and so is doing this only once.
You can rewrite like this and this should solve the issue:

Aggr(count(distinct churnedCustomer), Year-Month_Churn)
/

$(=Aggr(count(distinct churnedCustomer), Year_Churn)/num(month(now())))

[WhatsUp]

Hi Vincent, thanks for your reply. I tried your suggestion but unfortunately, it returns an error. The error  text showing is:

Aggr(count(distinct churnedCustomer), Year-Month_Churn)
/-

To specify: I get the yearly values but not the monthly ones, which I would like to have.

[vincent_ardiet_ · Accepted solution]

If your chart has [Year-Month_Churn] as a dimension (and if you have a single year of data), a simplified version of your expression should work:

count(distinct churnedCustomer)
/ ( count(TOTAL distinct churnedCustomer)/ count (TOTAL distinct [Year-Month_Churn) )