Price Variance calculation

[#1]

Hi,

I have a following table:

ArtCode and IVTRansDate are dimensions and following columns expressions.

GRQty and GRUPrice are data fields and for PriceVariance I used follwing calculation:

=GRUPrice-vFirstUPrice

Variable  vFirstUPrice=FirstSortedValue(GRUPrice,GRDate)

Everything is nice and working when I have one Item (ArtCode) selected. As soon as I have more than one item variable vFirstUPrice is not giving me any value and therefore my PriceVariance is empty.

I need to get table, where the first GRUPrice is taken and then for next rows variance is calculated according to the first price. I tried using Aggr function also, but did not succeed.

Any ideas, how it could be done?

Thanks in advance!

Mariliis

[#2]

Hi,

     Can you attach your qvw here?

-Jai

[#3]

Yes, sure. File is attached.

[SunilChauhan]

ArtCode is not Connected properly

if take data seperatly then u will find two different value inside

so check the proper columns to link

it will resolve ur issue.

[lironbaram]

hei

attach is an example

i calculate the first price

in the load script

take a look

[#6]

Thanks Iiron! This would work if the date is not selected, but if year 2011 for example is selected, then the variance should start again from the first price in year 2011, not from the very first price.

About ArtCode not being connected properly. I cannot see two values on the same day for same ArtCode. And even if there were, then it can be correct when two goods receipts were done at the same time.

[lironbaram · Accepted solution]

hei try this

i dont have time to think how not to display the rows with null

but other wise it seems ok

[#8]

Thank you so much! It is working just as I need

[qliksus]

Hi,

The solution of  Liron baram is good but if u want to do this in some other way give

GRUPrice-FirstSortedValue(total<ArtCode>GRUPrice,GRDate) in the Price Variance calculation

this will also give u the same result

[#10]

Thanks! It's actually easier solution and will try to remember this Total for the future as well.