Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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)
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
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?
i have updated my answer with a solution and exemple
Or use this expression (should be better)
FirstSortedValue(Quantity,-Date)
Regards
Vincent
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.
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)