Current month price to whole year

[dseelam]

Hello All,

How can I apply current month selling Price for all in months across year?- Pivot table 

In below example 6.8 is the price in January-2020 and from mid of jan price changed from 6.8 to 6.9 going forward months, not I want to apply 6.9 which is current month price across all months in 2020 which I should be only one line with selling price 6.9

total amount is units * selling price  

 

Thanks,

Dinesh 

[Vegar]

Try an expression similar to this: 

=$(=only(total {<Date = {"$(=maxstring(Date))"}>}aggr(only(Price), Date))) * Sum(Units)

See attached QVW file

[dseelam]

Thank you Vegar,

But I don't have any date column, all I have is Year, Month & Years. to use in expression

[Vegar]

I see. Lets try to solve it anyhow.

How do you identify which price is to be considered the latest price in your data set? You mention that you have two prices during the month of January?  Lets say you have a change of prices yesterday (April 12). How can you know which april price is the latest? 

[dseelam]

Hi Vegar,

You are right my bad , So I want to apply every month next month selling price across all years  since its forecast next month selling price will always have only one selling price.

[Vegar]

My question is still relevant. If you change prices mid april. How can you identify which price that should apply on the Mars transactions? 

[Vegar]

By assuming you have a transaction ID to identify which is the latest transaction price you could do as I've done in my attached example.

[Brett_Bleess]

Did Vegar's last post/example get you what you needed?  If so, we would appreciate it greatly if you would return to your thread and use the Accept as Solution button on that post to give him credit for the assistance and let other Members know that did work for you.  If you did something different, consider posting that and then use the button on that post to mark it.  

Regards,
Brett

[dseelam · Accepted solution]

Hello Brett,

Sorry for delayed Vegar's response did not work out for this requirement posted

I used below solution to achieve the requirement 

tmp:
LOAD Distinct
Key,
date(Min(Fo_Year_Month),'MMM-YYYY') as YM
Resident Demantra
Where Fo_Year_Month >= date(MonthStart(Now()),'MMM-YYYY')
Group by Key;

Left join (tmp)
LOAD Key,
Fo_Year_Month as YM,
[Selling Price] as SP_NEW
Resident Dem;