Announcements
MAINTENANCE ALERT: Dec. 12th starting 9 AM CET. Community will be read-only. GET DETAILS
cancel
Showing results for
Did you mean:
Partner - Contributor II

## Sum of Values from this date and 2 dates back

Hello everyone,

In a picture below, there is a problem I'm facing. I believe the solution can't be that hard, but iz seems that I'm missing it.

Simple Sales data (sales and calendar table in data model).

Straight table that contains "Date" dimension and  expression "sum of Quantity on that Date + Previous 2 Dates".

WantedValue in picture is something that I want, but can't figure out how.

Additional Note: Calculation should be aware that there could be data before 1.7.2016 and has to take it into account.

Thank you.

Regards,

Luka

1 Solution

Accepted Solutions
MVP

I have reduced data from your application and added few codes in script as well.

Regards,

MK

14 Replies
Creator

Hi,

Try this,

Simply use accumulate option(steps 2) for that expression.

or

By using rangesum() function also we can get the results.Use below expression

=RangeSum(above(sum(sales),0,2))

For example:

Partner - Contributor II
Author

Hi sravanthi aluvala,

thank you for answering. This partially solves the problem.

The question remains with two problems:

1. what if there is no sales on all dates. (I want data from this date and 2 previous dates, and not for this date and two previous dates that have sales documents (not all dates appear in table))

2. I selected July, but we have to be aware that there is data in Month before (June), and that data too has to be calculated. It means that a first row in table doesn't necessarily contain just it's own value, but maybe values from 30.6.2016 and 29.6.2016).

Regards,

Luka

Champion III

Can you try this?

= Sum({< Date = {'>= \$(=Date(Max(datefield)-2)) <= \$(=Date(Max(Datefield)))' } >} Sales)

Can you share a sample app to work on?

Partner - Contributor II
Author

Unfortunatelly no, this is not an answer.

I attached a sample app in Question above.

Regards,

Luka

MVP

Can you explain this by using one or two examples from your sample application?

Creator

Try this:

above(sum(Quantity),2)+above(sum(Quantity),1)+sum(Quantity)

Thanks,

Sangeetha

Creator III

Hi Luke,  may be try this

above(rangesum(below (sum(sales),0,2)),1)

Partner - Contributor II
Author

Sure,

1.

We had sales on 1.7.2016, on 2.7.2016 and 4.7.2016. We had no sales on 3.7.2016 (that means that 3.7.2016 will not show in dimension list). It means that a rangesum(above()) will not work, beacuse I want that this calculation takes 3.7.2016 in consideration also. On 4.7.2016 there has to be a Sum from 4.7.2016 + 3.7.2016 (it was zero) + value from 2.7.2016.

2.

On 30.6.2016 we sold a quantity of 176, on 1.7.2016 we sold 96 and on 2.7.2016 we sold 24.

Let's say that a front end user selects in his filters Year=2016 and Month=7.

For dimension value 2.7.2016 in my example I want a value 24+96+176=296

Regards,

Luka

Partner - Contributor II
Author

Close, but it still doesn't solve 2 problems that I mentioned in a reply to Sravanthi Aluvala.

Community Browser