Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
lukacvetko
Partner - Contributor II
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.

Pic1.PNG

Thank you.

Regards,

Luka

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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

Let me know whether this will help you or not.

Regards,

MK

View solution in original post

14 Replies
sravanthialuval
Creator
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))

11.PNG

For example:

12.PNG

lukacvetko
Partner - Contributor II
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

vishsaggi
Champion III
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?

lukacvetko
Partner - Contributor II
Partner - Contributor II
Author

Unfortunatelly no, this is not an answer.

I attached a sample app in Question above.

Regards,

Luka

MK_QSL
MVP
MVP

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

sangeess21
Creator
Creator

Try this:

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

Thanks,

Sangeetha

hemanthaanichet
Creator III
Creator III

Hi Luke,  may be try this 

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

lukacvetko
Partner - Contributor II
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

lukacvetko
Partner - Contributor II
Partner - Contributor II
Author

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