topic Re: Rangesum below is not working for second materal in QlikView

Rangesum below is not working for second materal

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi ,

I am applying an expression of rangesum below function to try to get the total detail value after a specific Cal Week as the value of Cal week, I use this expression

Concat(DISTINCT aggr( rangesum(below(sum(Detail), 1, 334)),[Cast Week],Material,Loc,[Cal Week]), '') ,

but it only works for first material, it doesn't work from second material, I have uploaded the qvw file. Can someone help me about this ?

Re: Rangesum below is not working for second materal

sunny_talwar — Mon, 04 Jun 2018 14:51:58 GMT

Where in the app are you using the above function? I see this expression

RangeSum(Above(Sum({<Dimension, Date= {'$(vDate)'}>}Failed),0,RowNo()))*Avg(1)

But where can we see this and the issue related with it?

Concat(DISTINCT aggr( rangesum(below(sum(Detail), 1, 334)),[Cast Week],Material,Loc,[Cal Week]), '')

Re: Rangesum below is not working for second materal

Anonymous — Tue, 05 Jun 2018 06:41:16 GMT

Hi Sunny,

Thanks for reminding, I think I uploaded the wrong qvw file, I attached the correct file, can you see it now?

Now my question is how to make expression that total of the details after the Cal week as the Rangsum of that Cal week

when Cal Week is greater than Cast Week , just like the attached screenshot:

Re: Rangesum below is not working for second materal

sunny_talwar — Tue, 05 Jun 2018 11:55:00 GMT

So, cast week is 2018044 and you want to perform the rangesum only after that week? Also, cast week is not a number field... do you have another field like cast week which looks like cal week?

Re: Rangesum below is not working for second materal

Anonymous — Tue, 05 Jun 2018 14:54:26 GMT

Hi Sunny,

I want to perform the rangsum when Cal Week is after 2018044, I updated the qvw file which cast week format is like cal week.

Re: Rangesum below is not working for second materal

sunny_talwar — Tue, 05 Jun 2018 19:23:38 GMT

Does this look like what you wanted?

RangeSum(Before(If([Cal Week] >= [Cast Week], Sum(Detail)), 0, 334))

Re: Rangesum below is not working for second materal

Anonymous — Wed, 06 Jun 2018 01:58:41 GMT

Hi Sunny,

Thanks for your expression, but it is not the expected result.

For an example:

For matl code AS72C/location code AU27:

from cal week 2018011 to 2018044, the expected value of Rangesum is 0 because only need to show value on cal week which is greater than cast week, cal week2018011 to 2018044 is not greater than cast week, so no need to show value of Rangsum or 0 for these cells ;

for cal week 2018051, its detail value 10248, but after cal week 2018051 all detail value is 0, so the Rangesum value on cal week 2018051 column is 0 because I need the details sum which cal week is greater than 2018051 till this row end.

I attached the expected result excel sheet, please check.

Re: Rangesum below is not working for second materal

qliksus — Wed, 06 Jun 2018 05:14:09 GMT

Something like the below

(sum(total<[Cast Week],[Rep Item],[Matl Code],[Location Code]> Detail) -

rangesum(before(sum(Detail), 0, ColumnNo())) ) * PurgeChar( [Cal Week]>trim(Replace([Cast Week],'WF','')),'-')

Re: Rangesum below is not working for second materal

Anonymous — Wed, 06 Jun 2018 07:29:42 GMT

Hi Susant,

Thanks very much for your expression, it works! But I have additional requirement based on the Rangesum of each cal week, that is to

show Rangesum of period and the value is the 4th week of each period, for example:

for matl code BP42T/location code AU00, Rangesum of cal week 2018054 is 25164, I need this value shown in 201805 column when I switch the dimension Cal Period. I attached the qvw file, please check.

Re: Rangesum below is not working for second materal

sunny_talwar — Wed, 06 Jun 2018 15:16:24 GMT

I don't understand your Excel file. All it is showing for Rangesum is 0 for all Cal Week

Re: Rangesum below is not working for second materal

qliksus — Thu, 07 Jun 2018 11:52:31 GMT

I am assuming you want to display the data from 201804 and show the last week accumulated value for that month . if so the below expression should be fine

(sum(total<[Cast Week],[Rep Item],[Matl Code],[Location Code]> Detail) -

sum(total<[Cast Week],[Rep Item],[Matl Code],[Location Code],[Cal Period]>Detail) ) * (

PurgeChar( [Cal Period] > only({<[Cal Period]={"=wildmatch(trim(Replace([Cast Week],'WF','')) , '*'&[Cal Period]&'*')=1"}>} total [Cal Period]),'-') )