topic Re: Price Variance calculation in QlikView

Price Variance calculation

Thu, 04 Aug 2011 10:05:25 GMT

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

Price Variance calculation

Thu, 04 Aug 2011 10:22:42 GMT

Hi,

Can you attach your qvw here?

-Jai

Re: Price Variance calculation

Thu, 04 Aug 2011 10:49:57 GMT

Yes, sure. File is attached.

Price Variance calculation

SunilChauhan — Thu, 04 Aug 2011 12:07:24 GMT

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.

Re: Price Variance calculation

lironbaram — Thu, 04 Aug 2011 12:11:27 GMT

hei

attach is an example

i calculate the first price

in the load script

take a look

Re: Price Variance calculation

Thu, 04 Aug 2011 13:00:32 GMT

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.

Re: Price Variance calculation

lironbaram — Thu, 04 Aug 2011 14:40:10 GMT

hei try this

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

but other wise it seems ok

Re: Price Variance calculation

Fri, 05 Aug 2011 08:05:52 GMT

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

Price Variance calculation

qliksus — Fri, 05 Aug 2011 12:19:34 GMT

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

Re: Price Variance calculation

Mon, 08 Aug 2011 08:35:57 GMT

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