topic Re: Problem with calculating expression in QlikView

Problem with calculating expression

Anonymous — Wed, 17 Jun 2015 13:08:56 GMT

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.

Re: Problem with calculating expression

sunny_talwar — Wed, 17 Jun 2015 13:31:03 GMT

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

Re: Problem with calculating expression

Anonymous — Wed, 17 Jun 2015 13:34:07 GMT

Script solution also works

Re: Problem with calculating expression

sunny_talwar — Wed, 17 Jun 2015 13:39:26 GMT

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:

Re: Problem with calculating expression

Anonymous — Wed, 17 Jun 2015 13:50:15 GMT

Perfect!

Thanks..

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

Re: Problem with calculating expression

sunny_talwar — Wed, 17 Jun 2015 14:02:30 GMT

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;