Filling missing values with previous data (File attached)

[#1]

Hello,

This is what I want to do:

I want to show the total stock in the past months. The stock is calculated using the current stock level with transaction history.

Problem is, when a month has no transactions, the stock level is 0. When calculating the total stock in that month, the total will be inaccurate.

Can someone please look at my Qlikview file? I have been struggling with this for the past 2 days.

Thanks in advance.

[simondachstr]

Maybe working with Full Accumulation would help in this case? This way, when there's no transaction the value will stay the same.

[#3]

But then all the values are accumulated...

[hic]

You should probably do this in the script using the peek() function. See more on

http://community.qlik.com/docs/DOC-3786 and

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/05/populating-a-sparsely-populated-field

HIC

[parthiband]

The Same Problem which i faced in last week..

Its very simple..

follow the link which Henric Cronstrom gave

Works fine

[#6]

I tried this numerous times.. the problem is, my calculations get all messed up and they dont work anymore. Besides, I have like 500 different types of articles, generating a timespan of 4 years for every date would take forever. Is this possible maybe once a month?

[Anonymous]

use the

above( [ total ] expression [ , offset [,n ]] ) function it helps

[#8]

Can you specify this? Where should I use it?

[#9]

I have the following code:

TempTable_Rates:

Load           [Date],

                    [KeyPart]

Resident [Stock];

MinMaxDate:

Load Min(Date) as MinDate, Max(Date) as MaxDate resident TempTable_Rates;

Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;

Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate')    ;

Drop Table MinMaxDate;

Join (TempTable_Rates)

Load Date(recno()+$(vMinDate)) as [Date] Autogenerate vMaxDate - vMinDate;

[FinalStock]:

NoConcatenate Load

          date([Date]) as [Date],

          [KeyPart]

Resident TempTable_Rates;

RIGHT JOIN

LOAD

                    [Date],

                    [KeyPart],

                    [PartStockRecord],

                    If( IsNull( [MutationValue] ), Peek( [MutationValue] ), [MutationValue] ) as [MutationValue],

                    [PartStock],

                    [PartStockStart],

                    [PartStockEnd],

                    [CodeGroep]

Resident [Stock]

ORDER BY [KeyPart], Date DESC;

Drop Table TempTable_Rates;

Drop Table [Stock];

But it does not join the tables. It just shows an empty list of dates before showing the actual values. What am i doing wrong?

[Anonymous]

Hi

Please look at the attached file

In the expressions tab I have used the following expression

=if(sum(cost)='0',Above (sum(cost)),sum(cost))

Regards

Harsha