Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All, I need to do something it may be simple but so far I'm no able to get it. So basically what I want is illustrated in the "AccumulatedCount" column below:
Key | StartDate | EndDate | Count | AccumulatedCount |
---|---|---|---|---|
1 | 10/02/2018 | 12/02/2018 | 1 | 1 |
3 | 11/02/2018 | 13/02/2018 | 1 | 2 |
4 | 12/02/2018 | 14/02/2018 | 1 | 3 |
5 | 13/02/2018 | 15/02/2018 | 1 | 4 |
5 | 14/02/2018 | 16/02/2018 | 1 | 5 |
The closest shoot I got so far is use this expression:
if(StartDate< Previous(EndDate), Count+1, Previous(Count)) AS AccumulatedCount
But, of course that's not what I want cause I'm summing 1 to 1 all the time:
Key | StartDate | EndDate | Count | AccumulatedCount |
---|---|---|---|---|
1 | 10/02/2018 | 12/02/2018 | 1 | 1 |
3 | 11/02/2018 | 13/02/2018 | 1 | 2 |
4 | 12/02/2018 | 14/02/2018 | 1 | 2 |
5 | 13/02/2018 | 15/02/2018 | 1 | 2 |
5 | 14/02/2018 | 16/02/2018 | 1 | 2 |
So I need something like that expression but over the AccumulatedCount field but not sure how to do it. Hope it makes sense.
Cheers,
Andrés
You might have more than one dimensions in there.... Try this
RangeSum(Above(TOTAL SessionCount, 0, RowNo(TOTAL)))
Try this
RangeSum(Peek('AccumulatedCount'), 1) as AccumulatedCount
It doesn't work. The problem really is that you need to add 1 every single time the conditions fulfills (i.e. a simple a++ in any programming language) but here in the load editor you can't just do that. So I'm not sure if this is doable or you need to do it in the chart instead (although the same problem might happen there).
Not sure how to solve it.
You can, try this
If(StartDate < Previous(EndDate), RangeSum(Peek('AccumulatedCount'), 1), Peek('AccumulatedCount')) as AccumulatedCount
That's exactly what I tried before. But something weird is happening or I'm missing something cause even in a table chart using the below expression and simplifying it without taking into account the date fields:
RangeSum(Above(SessionCount,0))
Which should work to gave me and accumulated value, it's just displaying always 1.
The same thing happens in the script if I use peek.
Not really, you have not used RowNo()
RangeSum(Above(SessionCount, 0, RowNo()))
Indeed, you're right but still not working:
You might have more than one dimensions in there.... Try this
RangeSum(Above(TOTAL SessionCount, 0, RowNo(TOTAL)))
Perfect! Didn't know you need to do that when you have more than 1 dimension. Thanks a lot, you've been very helpful!
Cheers,
Andrés
But this will continue to accumulate regardless of change in any of your dimensions.... if that is what you want, then that is great... else you will have to play around with your expression to make it to work