topic Cumulative Month Sum in KPI Object in QlikView

Cumulative Month Sum in KPI Object

jyotiwagde — Thu, 17 Jan 2019 11:29:08 GMT

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.

Re: Cumulative Month Sum in KPI Object

sunny_talwar — Thu, 17 Jan 2019 12:18:46 GMT

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

Re: Cumulative Month Sum in KPI Object

jyotiwagde — Thu, 17 Jan 2019 14:00:48 GMT

Hi Sunny,

Thanks for giving your precious time to look after this.

Please find excel sample file.

Re: Cumulative Month Sum in KPI Object

sunny_talwar — Thu, 17 Jan 2019 14:01:35 GMT

You can zip the qvf and then attach it

Re: Cumulative Month Sum in KPI Object

jyotiwagde — Thu, 17 Jan 2019 14:04:39 GMT

Re: Cumulative Month Sum in KPI Object

sunny_talwar — Thu, 17 Jan 2019 14:34:45 GMT

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))))

Re: Cumulative Month Sum in KPI Object

jyotiwagde — Fri, 18 Jan 2019 08:01:35 GMT

Hi Sunny,

Thank You so much for the help.

It work fine.

Re: Cumulative Month Sum in KPI Object

jyotiwagde — Wed, 13 Feb 2019 05:29:34 GMT

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.

Re: Cumulative Month Sum in KPI Object

sunny_talwar — Tue, 12 Feb 2019 13:14:04 GMT

What is your expression for Cumml column?

Re: Cumulative Month Sum in KPI Object

jyotiwagde — Wed, 13 Feb 2019 05:28:42 GMT

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.

Re: Cumulative Month Sum in KPI Object

sunny_talwar — Fri, 15 Feb 2019 14:35:28 GMT

How about something like this

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