topic Re: Sum of Values from this date and 2 dates back in QlikView

Sum of Values from this date and 2 dates back

lukacvetko — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Sum of Values from this date and 2 dates back

sravanthialuval — Tue, 31 Jan 2017 14:07:48 GMT

Hi,

Try this,

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

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

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

For example:

Re: Sum of Values from this date and 2 dates back

lukacvetko — Tue, 31 Jan 2017 14:29:56 GMT

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

Re: Sum of Values from this date and 2 dates back

vishsaggi — Tue, 31 Jan 2017 14:38:26 GMT

Can you try this?

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

Can you share a sample app to work on?

Re: Sum of Values from this date and 2 dates back

lukacvetko — Tue, 31 Jan 2017 14:54:54 GMT

Unfortunatelly no, this is not an answer.

I attached a sample app in Question above.

Regards,

Luka

Re: Sum of Values from this date and 2 dates back

MK_QSL — Tue, 31 Jan 2017 15:13:42 GMT

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

Re: Sum of Values from this date and 2 dates back

sangeess21 — Tue, 31 Jan 2017 16:28:07 GMT

Try this:

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

Thanks,

Sangeetha

Re: Sum of Values from this date and 2 dates back

hemanthaanichet — Tue, 31 Jan 2017 17:44:44 GMT

Hi Luke, may be try this

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

Re: Sum of Values from this date and 2 dates back

lukacvetko — Wed, 01 Feb 2017 08:36:08 GMT

Sure,

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.

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

Re: Sum of Values from this date and 2 dates back

lukacvetko — Wed, 01 Feb 2017 08:38:46 GMT

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

Re: Sum of Values from this date and 2 dates back

lukacvetko — Wed, 01 Feb 2017 08:39:10 GMT

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

Re: Sum of Values from this date and 2 dates back

MK_QSL — Wed, 01 Feb 2017 09:38:00 GMT

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,

Re: Sum of Values from this date and 2 dates back

lukacvetko — Wed, 01 Feb 2017 10:03:42 GMT

Manish,

Thank you very much. I believe this is a right solution.

Just one additional question, why *avg(1) ?

Regards,

Luka

Re: Sum of Values from this date and 2 dates back

MK_QSL — Wed, 01 Feb 2017 10:06:22 GMT

It's a dummy field which will not make any changes.. but without that if you select any month, year or any calendar field, it will not ignore those lines..

Just remove Avg(1) from my expression and select any monthyear and see the effect. You will understand.

Re: Sum of Values from this date and 2 dates back

lukacvetko — Wed, 01 Feb 2017 10:08:39 GMT

Understood. Thanks again.