Cumulative Month Sum in KPI Object

[jyotiwagde]

Hi All,

I have the below table,  New Adv. column contains the data for the advisor who are freshly hired every month.

Cumml column having the running total for each month.

Need to show the total showing at top row on KPI object.

let say we are selecting Dec FY2019 then the KPI should give me the value 69+160+163+199=591

Thanks in advance.

[sunny_talwar]

Would you be able to share a sample to help you better?

[jyotiwagde]

Hi Sunny,

Thanks for giving your  precious time to look after this.

Please find excel sample file.

[sunny_talwar]

You can zip the qvf and then attach it

[sunny_talwar · Accepted solution]

Try this

Sum({<[Link Date]={"<=$(=Max([Link Date]))"},[Member Type]={'HM'},FYear=,Month=,FQtr=>} Aggr(
RangeSum(Above(Total Sum({<[Link Date]={"<=$(=Max([Link Date]))"},[Member Type]={'HM'},FYear=,Month=,FQtr=>} [Head Count]),0,RowNo(TOTAL))),
([Month Start], (NUMERIC))))

[jyotiwagde]

Hi Sunny,

Thank You so much for the help.

It work fine.

[jyotiwagde]

Hi all,

In the above application only, i want show the cumulative sum for employee hired on YTD & QTD wise.

For eg. given image is showing the hired employees each month , i want to show data in the straight table where it should have to show the total 72 against the particular empolyee.

[sunny_talwar]

What is your expression for Cumml column?

[jyotiwagde]

Expression for Cumml column:

RangeSum(Above(Total Sum({<[Link Date]={"<=$(=Max([Link Date]))"},[Member Type]={'HM'},FYear=,Month=,FQtr=>} [Head Count]),0,RowNo(TOTAL)))

however it is taking above of the previous record data of different emp. I have also aggr based on the emp still not working.

I want the output like the img attached.