last stock value per Items in warehouses

[#1]

Hello,

I need to create a stratight table displaying the last entered value for an item but I dont seem to get around it. I have seen other questions like this but they have still not been answered.

I need to create a list displaying each Item and its last entered value. In my source data I have "Revision date" that I could use but I dont want to filter in extraction but rather do this in a set analysis.

had it been in SQL I would have dome something like:

select A, B,  Max(Date)

Group By A,B

Resultset would be:

Item 1, 100, 2012-12-31

Item 2, 60, 2013-05-04

Item 3, 140, 2013-11-07

How do I get this in a chart in QV?

similar problem as in Stock management - How to extract a stock value from a maximum date. but this is not answered...

[Anonymous]

Hi Hanna,

If you have stock pictures on the same date for all items, use a set analysis as following:

=Sum({$<Date={'$(=Max(Date))'}>}Quantity)

If Max date is not the same for items use this one:

=Sum({$<Date={'=Aggr(Max(Date),Item)'}>}Quantity)

Please find attached an exemple based on your data set

Regards

Vincent

[#3]

Hello Vincent,

Unfortunately this is not the case, values can change at different times for different items.

I managed to accomplish to only select the last reported date and then present the Item for that but it was the last item not all items. I need to display a value (the last reported one) for each item.

Any ideas?

[Anonymous]

i have updated my answer with a solution and exemple

[Anonymous]

Or use this expression (should be better)

FirstSortedValue(Quantity,-Date)

Regards

Vincent

[mdmukramali]

Dear,

Kindly find the attachment.

In script level create a table by using resident load and apply the group by clause.

like,

LOAD A,Date(Max(Date)) as MaxDate

Resident ITEMS

Group by A;

Thanks,

Mukram.

[#7 · Accepted solution]

Thank you Vincent!

I used the two as it did not turn out correct with only one of the calculations:

({<factFADebt_UpdatedDate= {"=Max(factFADebt_UpdatedDate)"}>}[Maximum Value], -factFADebt_UpdatedDate)