QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
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
MVP

Re: Problem with calculating expression

Try this script:

Table:

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

FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1);

Output in Table Box:

5 Replies
MVP

Re: Problem with calculating expression

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

New Contributor III

Re: Problem with calculating expression

Script solution also works

MVP

Re: Problem with calculating expression

Try this script:

Table:

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

FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1);

Output in Table Box:

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....)

MVP

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:

FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1);

Table1: