Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

andreyfcdk91
New Contributor III

Problem with calculating expression

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.

1 Solution

Accepted Solutions

Re: Problem with calculating expression

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:

Capture.PNG

5 Replies

Re: Problem with calculating expression

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

andreyfcdk91
New Contributor III

Re: Problem with calculating expression

Script solution also works

Re: Problem with calculating expression

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:

Capture.PNG

andreyfcdk91
New Contributor III

Re: Problem with calculating expression

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

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;

Community Browser