Last 7 Days Sum for Every Date along with Conditions

[#1]

Hi All

How to get Last 7 Days Sum Along with other conditions for each and every date from Script.

Is this Possible.

Logic Requirement:

Raw Data File is Attached for more understanding.

[#2]

Can you attach a sample qvw ?

[#3]

How the Logic Works:

1) 100+200+300+400+500+600+700=2800 (From Cell C2 to C7)

2) 400 Where Weekday=1 (From Cell C2 to C7)

3) 200 where Weekday=6 (From Cell C2 to C7)

So, Final Calculation = 2800-400-200 = 2200/5 = 440

The same Logic is applicable to each and every Date.

[qlikview979]

Hi Try this,

T1:

LOAD

Date,

  weekday,

Value,

Requirement,

[Formula Used]

FROM

(ooxml, embedded labels, table is Sheet1);

Let  Vpeek=peek('Date',-6,'T1');

T2:

LOAD

Date,

  weekday,

Value

Resident T1 Where Date>='$(Vpeek)';

DROP Table T1;

[#5]

Anyone have solution to this.

stalwar1‌

swuehl‌

gwassenaar‌

Sir Please help me on this.

This is something new to me.

I have tried with Rangesum() and Peek() Function but no Luck.

Regards

Eric

[sunny_talwar]

This?

[#7]

Thank You Sir.

This is Perfect Solution.

But I want this from Back end Script.

Is this Possible.

Regards

Eric

[sunny_talwar · Accepted solution]

You can try this:

Table:

LOAD Date,

    weekday,

    Value

FROM

[Requirement Excel Formula.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD *,

  If(RowNo() > 7, RangeSum(If(not Match(Peek('weekday', -1), 1, 6), Peek('Value', -1)),

  If(not Match(Peek('weekday', -2), 1, 6), Peek('Value', -2)),

  If(not Match(Peek('weekday', -3), 1, 6), Peek('Value', -3)),

  If(not Match(Peek('weekday', -4), 1, 6), Peek('Value', -4)),

  If(not Match(Peek('weekday', -5), 1, 6), Peek('Value', -5)),

  If(not Match(Peek('weekday', -6), 1, 6), Peek('Value', -6)),

  If(not Match(Peek('weekday', -7), 1, 6), Peek('Value', -7)))/5) as CumulativeValue

Resident Table;

DROP Table Table;