6 Replies Latest reply: Feb 8, 2018 12:47 AM by Shahzad Ahsan

# Cumulative line chart with year month and Employee wise

Hi Friends

I am using cumulative line chart. I have managed to calculate by year and Employee wise but I need month wise also.

I am using drill down dimension for year month. Suppose if I click on any year then it should show month wise data.

Please see the attachment.

I am using this

Dimension:

1. Year_Month(Drill down)

2. Employee Name

Measure:

Aggr(RangeSum(Above(Sum(if(SUBMIT_GRN_AMOUNT=0 and PO_TND='TND', GRN_AMOUNT)),0,RowNo())),EMPLOYEE_NAME,yr_d)

Script:

[Logistics]:

Load *, Year(DELIVER_DATE) as yr_d,Month(DELIVER_DATE) as Month_d;

SQL select * from REC_SUBMIT_KPI

TempTable:

LOAD Distinct yr_d

Resident Logistics;

Left Join (TempTable)

LOAD Distinct EMPLOYEE_NAME

Resident Logistics;

Concatenate (Logistics)

LOAD *

Resident TempTable;

DROP Table TempTable;

• ###### Re: Cumulative line chart with year month and Employee wise

stalwar1 Please, I need your help

• ###### Re: Cumulative line chart with year month and Employee wise

It would be difficult to help without seeing a sample, would you be able to share a sample to check this out?

• ###### Re: Cumulative line chart with year month and Employee wise

Hi Sunny

Thank you for your reply

Here is my sample data in excel

Dimension:

1. Year_Month(Drill down)

2. Emp Name

Measure:

Aggr(RangeSum(Above(Sum(AMOUNT)),0,RowNo())),EmpName,yr)

Script

[Logistics]:

Load *, Year(DELIVER_DATE) as yr,Month(DELIVER_DATE) as mnt;

SQL select * from Sample Excel

TempTable:

LOAD Distinct yr

Resident Logistics;

Left Join (TempTable)

LOAD Distinct EMP_NAME

Resident Logistics;

Concatenate (Logistics)

LOAD *

Resident TempTable;

DROP Table TempTable;

I have already calculated cumulative line chart by empname and Year (Please check attached snapshot in question).

What I need is , I want by empname, year , month, . If I select any year, it should show by month of that selected year.

• ###### Re: Cumulative line chart with year month and Employee wise

Please check this sample qvf file.

Please modify in this.

Year sorting is not working in this

• ###### Re: Cumulative line chart with year month and Employee wise

Try this

If(GetSelectedCount(yr) <> 1,

Aggr(RangeSum(Above(Sum(AMOUNT), 0, RowNo())), [Emp Name], (yr, (NUMERIC))),

Aggr(RangeSum(Above(Sum(AMOUNT), 0, RowNo())), [Emp Name], (mnt, (NUMERIC))))

• ###### Re: Cumulative line chart with year month and Employee wise

Great Buddy

This is working

Thank You so much...I really appreciate it