Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Empty fields in table

Hello,

I have a table called "Inventory cost" (pic 1)

1.png

Another table called "remained in stock" (pic 2)

4.png

So, I have to make final table

3.png

, where:

quantity in the begining calculated like

RangeSum(

  Sum({$< InOrOut = {0}, DateMovement = {'<=$(vMinSelectedDate)'}, Date >} Quantity),

  Sum({$< InOrOut = {1}, DateMovement = {'<=$(vMinSelectedDate)'}, Date >} Quantity*(-1))

)

Fields Quantity in, out and in the end calculated similarly.


Field "avg cost":

avg( aggr(sum(DISTINCT(cost +quantity)),Date, Item, DateItemCame))

The problem is that some fields in column avg cost is missed. Everything else calculated excellent.

Please help!

I'm new in qlikview. And sorry for my english)

11 Replies
bbi_mba_76
Partner - Specialist
Partner - Specialist

Hi,

how the two tables are joined?

I see both item and quantity in the two tables.

I suppose you are working with the data of the secont table and using field from the first is not working fine.

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

I think your expression for average cost should look something like:

avg(aggr(sum(Quantity / Cost), Item, DateItemCame))

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Two tables connected by the field - Item.

Quantity columns are differs. I'm sorry for not clear question.

You are right, I use 2nd table and use fields from 1st to calculate the average cost

Not applicable
Author

I've tried to use it. But the result was the same.

I think the problem is with Date.

For example, if I not using Date in columns "Quantity on the beginning" and etc the Average column shows everything perfect. But not using Date, the quantity calculates incorrectly.

jonathandienst
Partner - Champion III
Partner - Champion III

How would you like the movement date to relate to the cost date?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Thank you, Jonathan, for reply.

Actually they are not related. I don't want to relate them. Two tables joined only by "Item". What I want is to take 2nd table as base and take some columns from 1st to calculate the average cost

Not applicable
Author

I made a mistake in posting, of course I use spending instead of quantity)..

So I calculate avg cost like:

=avg( aggr(sum(DISTINCT(cost +spending)),Date, Item, DateItemCame)).. Works fine, but misses some Items. Why???.. I don't understand(

jonathandienst
Partner - Champion III
Partner - Champion III

Well, if the quantity, cost and item all come the first table, and the item field has the same name as in the second table, and if it is only key between the two tables, then I see no reason that expression does not work. You might need to post a more detailed example, with the correct field names, so that I can better understand your data structure.

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

Iskander Smagulov wrote:

So I calculate avg cost like:

=avg( aggr(sum(DISTINCT(cost +spending)),Date, Item, DateItemCame)).. Works fine, but misses some Items. Why???.. I don't understand(

I dont understand what you are trying to calculate with that expression. Using distinct will only consider one cost+spending amount, even if there are several for different items. Perhaps you mean

     avg(aggr(DISTINCT Sum(...

Also, if either cost or spending is null or is missing, then cost+spending will evaluate to null. So use RangeSum(cost , spending) in place of cost+spending.

I hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein