Problem with calculating expression

[Anonymous]

Hi everyone!

I have some problem with correct calculating expression in my model.

I have pivot table, where i calculate expression result by dimension YearMonth as:

=sum(Quantity*Sum)

There is Quantity in every YearMonth, but not Sum.

What i need: If there no sum for some YearMonth, get it from nearest previous YearMonth.

For example:

In YearMonth 201505 there no sum, that's why in this row i need get neares sum from YearMonth 201504 = 22*4=88

In YearMonth 201506 there no sum, that's why in this row i need get neares sum from YearMonth 201504 = 12*4=48

There're model and source file for my task.

Please, help to solve this problem.

[sunny_talwar]

Do you need a solution on the front end or script solution also works?

[Anonymous]

Script solution also works

[sunny_talwar · Accepted solution]

Try this script:

Table:

LOAD *,

  If(Len(Trim(Sum)) = 0, Peek('Sum1'), Sum) as Sum1;

load *

FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1);

Output in Table Box:

[Anonymous]

Perfect!

Thanks..

Tell me please, will this calculation aggregate by id? (this is test variant of task, there will be more values....)

[sunny_talwar]

Not sure I understand your question??? This will populate missing value for whereever there is a missing sum. The only thing I would add is to order it (in a resident table instead of preceding load) correctly when you have more data . may be like this:

Table:

LOAD *

FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1);

Table1:

LOAD *,

  If(Len(Trim(Sum)) = 0, Peek('Sum1'), Sum) as Sum1

Resident Table

Order By Id, YearMonth;

Drop Table Table;