Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
Not applicable

Filling missing values with previous data (File attached)

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.

10 Replies
Luminary
Luminary

Re: Filling missing values with previous data (File attached)

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

Not applicable

Re: Filling missing values with previous data (File attached)

But then all the values are accumulated...

Re: Filling missing values with previous data (File attached)

Partner
Partner

Re: Filling missing values with previous data (File attached)

The Same Problem which i faced in last week..

Its very simple..

follow the link which Henric Cronstrom gave

Works fine

Not applicable

Re: Filling missing values with previous data (File attached)

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?

sriharsha96
Contributor II

Re: Filling missing values with previous data (File attached)

use the

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

Not applicable

Re: Filling missing values with previous data (File attached)

Can you specify this? Where should I use it?

Not applicable

Re: Filling missing values with previous data (File attached)

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?

sriharsha96
Contributor II

Re: Filling missing values with previous data (File attached)

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