topic Re: Aggregate minimal date with minimal price problem! in QlikView

Aggregate minimal date with minimal price problem!

Mon, 15 Sep 2014 08:06:34 GMT

Hello guys,

i have a little problem. I want to get the date when a person buyed a specific item with the MINIMAL price. The data looks like this:

Item	Price	Date
x	12	12.01.2014
x	23	17.02.2014
x	45	16.03.2014
y	123	10.10.2005
y	123	20.12.2013
y	123	05.03.2012

So i want to get that product x was bought with the minimal price on 12.01.2014 and product y on 10.10.2005.

I've tried with the Firstsortedvalue() it works okay but when i put that date into set analysis -> Sum({<Date=(>=("FirstSortedValue(...)>} quantity) i get nothing...

So is there any solution to get that date during a script load ?

Thanks in advance!

Jacek

Re: Aggregate minimal date with minimal price problem!

tresB — Mon, 15 Sep 2014 08:17:59 GMT

Try like:

=FirstSortedValue(Aggr(Sum(Price),Date), Date)

Re: Aggregate minimal date with minimal price problem!

Mon, 15 Sep 2014 08:23:12 GMT

I want to just get the min date when the product was bought with the min price

Re: Aggregate minimal date with minimal price problem!

jagan — Mon, 15 Sep 2014 08:31:13 GMT

Hi,

Try like this

Data:

LOAD

If(Previous(Item) <> Item, 1, 0) AS LowestPriceFlag;

LOAD

FROM DataSource

ORDER BY Item, Price, Date;

Now LowestPriceFlag column has lowest date.

In set analysis try like this

Dimension: Item

Expression : Only({<LowestPriceFlag={1}>} Date)

Hope this helps you.

Regards,

jagan.

Re: Re: Aggregate minimal date with minimal price problem!

tresB — Mon, 15 Sep 2014 08:59:06 GMT

It should be easier:

=FirstSortedValue(distinct Date, Price)

PFA

Re: Aggregate minimal date with minimal price problem!

martinpohl — Mon, 15 Sep 2014 09:02:27 GMT

Hi,

set analysis won't work with this problem.

Use a formula like this

sum( if( aggr(nodistinct min(Date), Item) = Date, Price, 0))

within an chart with Item as dimension.

Regards

Re: Aggregate minimal date with minimal price problem!

MK_QSL — Mon, 15 Sep 2014 09:34:49 GMT

=Date(FirstSortedValue(Distinct Date, Price))