Sum of Values from this date and 2 dates back

[lukacvetko]

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

[sravanthialuval]

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:

[lukacvetko]

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

[vishsaggi]

Can you try this?

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

Can you share a sample app to work on?

[lukacvetko]

Unfortunatelly no, this is not an answer.

I attached a sample app in Question above.

Regards,

Luka

[MK_QSL]

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

[sangeess21]

Try this:

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

Thanks,

Sangeetha

[hemanthaanichet]

Hi Luke,  may be try this 

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

[lukacvetko]

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

[lukacvetko]

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