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: 
Anonymous
Not applicable

Rangesum below is not working for second materal

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 ?

10 Replies
sunny_talwar

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]), '')

Anonymous
Not applicable
Author

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:

sunny_talwar

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?

Anonymous
Not applicable
Author

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.

sunny_talwar

Does this look like what you wanted?

Capture.PNG

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

Anonymous
Not applicable
Author

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.

qliksus
Specialist II
Specialist II

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

Anonymous
Not applicable
Author

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.

sunny_talwar

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

Capture.PNG