Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Shahzad_Ahsan
Creator III
Creator III

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;

1 Solution

Accepted Solutions
sunny_talwar

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


Capture.PNG

View solution in original post

6 Replies
Shahzad_Ahsan
Creator III
Creator III
Author

stalwar1‌ Please, I need your help

sunny_talwar

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

Shahzad_Ahsan
Creator III
Creator III
Author

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.

Shahzad_Ahsan
Creator III
Creator III
Author

Please check this sample qvf file.

Please modify in this.

Year sorting is not working in this

sunny_talwar

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


Capture.PNG

Shahzad_Ahsan
Creator III
Creator III
Author

Great Buddy

This is working

Thank You so much...I really appreciate it