Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Highlighted
Luminary
Luminary

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

Highlighted
Not applicable

But then all the values are accumulated...

Highlighted

Highlighted
Partner
Partner

The Same Problem which i faced in last week..

Its very simple..

follow the link which Henric Cronstrom gave

Works fine

Highlighted
Not applicable

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?

Highlighted
Creator II
Creator II

use the

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

Highlighted
Not applicable

Can you specify this? Where should I use it?

Highlighted
Not applicable

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?

Highlighted
Creator II
Creator II

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