Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Price Variance calculation

Hi,

I have a following table:

Screen shot 2011-08-04 at 12.52.34 PM.png

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

1 Solution

Accepted Solutions
lironbaram
Honored Contributor II

Re: Price Variance calculation

hei try this

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

but other wise it seems ok

9 Replies
Not applicable

Price Variance calculation

Hi,

     Can you attach your qvw here?

-Jai

Not applicable

Re: Price Variance calculation

Yes, sure. File is attached.

chauhans85
Esteemed Contributor

Price Variance calculation

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
Honored Contributor II

Re: Price Variance calculation

hei

attach is an example

i calculate the first price

in the load script

take a look

Not applicable

Re: Price Variance calculation

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
Honored Contributor II

Re: Price Variance calculation

hei try this

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

but other wise it seems ok

Not applicable

Re: Price Variance calculation

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

qliksus
Valued Contributor

Price Variance calculation

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

Not applicable

Re: Price Variance calculation

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

Community Browser