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: 
omid5ive
Creator II
Creator II

Accumulated chart

hi

i have table with request id and date

like attached

i want to show accumulated count of request on line chart with group dim, this group contain year and month

when i check the full accumulation everything is ok but when i drill down to month my first month of selected year start to count from zero

for example if i have 5 request in 2017 and i have 1 request in the first month of 2018 i want to have a 6 for  start of 2018

what can i do?

 

 

1 Solution

Accepted Solutions
sunny_talwar

Are you using QV12 or above... if you are... then try this expression

If(GetSelectedCount(year) > 0,
Aggr(RangeSum(Above(Count(DISTINCT {<year, month, YearMonth>} requestid), 0, RowNo())), (YearMonth, (NUMERIC))),
RangeSum(Above(Count(DISTINCT requestid), 0, RowNo())))

Where YearMonth field is created in the script like this

LOAD *,
	 Date(Date#(year*100 + month, 'YYYYMM'), 'YYYYMM') as YearMonth;
LOAD * INLINE [
    requestid, year, month
    100, 2018, 1
    101, 2018, 2
    102, 2017, 3
    103, 2017, 1
    104, 2017, 5
    105, 2017, 3
    106, 2018, 4
];

View solution in original post

5 Replies
sunny_talwar

Are you using QV12 or above... if you are... then try this expression

If(GetSelectedCount(year) > 0,
Aggr(RangeSum(Above(Count(DISTINCT {<year, month, YearMonth>} requestid), 0, RowNo())), (YearMonth, (NUMERIC))),
RangeSum(Above(Count(DISTINCT requestid), 0, RowNo())))

Where YearMonth field is created in the script like this

LOAD *,
	 Date(Date#(year*100 + month, 'YYYYMM'), 'YYYYMM') as YearMonth;
LOAD * INLINE [
    requestid, year, month
    100, 2018, 1
    101, 2018, 2
    102, 2017, 3
    103, 2017, 1
    104, 2017, 5
    105, 2017, 3
    106, 2018, 4
];
omid5ive
Creator II
Creator II
Author

would you please explain what is NUMERIC?

omid5ive
Creator II
Creator II
Author

sorry

would you please check my attached file

my calendar is jalali i dont think that can effect on the correction

but when you dont select any thing you can see that we have 286 request (in year 1396 we have 102 and in year 1397 we have 184) the accumulative equal 286

but when you see the last month of 1397 year you can see 289

 

omid5ive
Creator II
Creator II
Author

thank you sunny

i found the problem

my problem is when i have a request in 2 month

for example

request 100 in 201101 and 201102