topic rangesum cumulative value not as expected in QlikView

rangesum cumulative value not as expected

tracysmart — Thu, 20 Oct 2016 19:25:51 GMT

Hi All

can anyone explain why my rangesum isn't returning the correct cumulative figure in my Delivered chart while it is in my RDD and FDD chart. The date dimension is in the same order as I force the order with a load statement.

Am I missing something straightforward?

Thanks in advance

Tracy

Re: rangesum cumulative value not as expected

sunny_talwar — Thu, 20 Oct 2016 19:33:44 GMT

I think you had a closing parenthesis at incorrect location... Try this:

RangeSum(Above(Count({<PassFail={'Fail'}, Type={'Delivered'}>} COVLOOKUP), 0, RowNo()))

Instead of this

RangeSum(Above(Count({<PassFail={'Fail'}, Type={'Delivered'}>} COVLOOKUP)), 0, RowNo())

Re: rangesum cumulative value not as expected

tracysmart — Thu, 20 Oct 2016 19:36:10 GMT

Thanks again Sunny

I think the fact that I have been working for 12 hours straight through is having an impact!!

Tracy

Re: rangesum cumulative value not as expected

sunny_talwar — Thu, 20 Oct 2016 19:37:13 GMT

Hahahaha sure looks like that

Re: rangesum cumulative value not as expected

tracysmart — Thu, 20 Oct 2016 19:54:42 GMT

can I ask your advice on how you would go about combining the 2 bar charts together? I think I will leave until the morning but any pointers would be greatly received!

The client does it manually in excel at the moment. it looks like this in excel

Re: rangesum cumulative value not as expected

sunny_talwar — Thu, 20 Oct 2016 20:28:29 GMT

Try this expression may be (without full accumulation)

Pick(Dim,

Aggr(RangeSum(Above(if(Source='Current', count(COVLOOKUP),

above((sum(aggr(sum(above(count({<Week_Combined=>}COVLOOKUP), 0)),Week_Combined, OverdueAge ))))

- count(COVLOOKUP)

), 0, RowNo())), OverdueAge, Week_Combined),

Aggr(NODISTINCT If(WeekOverDue = Week_Combined,

RangeSum(above(TOTAL count({<PassFail={'Fail'}, Type={'Delivered'}>}COVLOOKUP), 0, rowno(TOTAL)))

RangeSum(above(TOTAL count({<PassFail={'Fail'}, Type={'Failed'}>}COVLOOKUP), 0, rowno(TOTAL)))), WeekOverDue, Week_Combined))

Re: rangesum cumulative value not as expected

tracysmart — Fri, 21 Oct 2016 07:42:14 GMT

Sunny, thank you for this.

This is perfect. I have not done anything like this before and its really useful to know this is possible. I doubt I would have got to this solution quickly on my own.

I have given you all the credit with my Client

Tracy

Re: rangesum cumulative value not as expected

sunny_talwar — Fri, 21 Oct 2016 10:34:09 GMT

Hahahaha I am awesome I was able to help I would just you to take some time to mark helpful responses, if you think it will be helpful for others to see.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny