Null result firstsortedvalue flexible (N), lowest price N-invoices

[robbybalboa]

Dear Group,

A want to create a diagramm

- showing flexible the lowest selling prices from N-invoices (e.g. from last 3 invoices the cheapest price)

- grouped by customer and item

- dynamic usable - solution with script counter ... not possible

- sell price 0 excluded

I don't want to add for example three firstsortedvalue formula to get the result. Approach should be very flexible.

I thought to get with firstsortedvalue my invoices as a starting point an then with these invoices I get the lowest price.

Firstsortedvalue creates NULL result if there are less then 3 invoices.
I want to cover this problem with using an count-function - but unfortunately this does not work.

The red marked formula does not work.

=firstsortedvalue(Distinct {<price_pcs-={'0'}>}   

aggr(min(price_pcs),   

date, customer, item),-date, 

rangemin($(vDAY_BACK),count({<price_pcs-={0}>}Distinct date))

)

Any ideas why?

Or does someone knows a better idea to get the correct result?

Thank you very much for your help

Robert

[sunny_talwar]

I think the rank has to be a static number and cannot use an expression. May be try this

=If(Count({<price_pcs-={0}>}Distinct date) = 1,

firstsortedvalue(Distinct {<price_pcs-={'0'}>}   

aggr(min(price_pcs), date, customer, item),-date, 1),

If(Count({<price_pcs-={0}>}Distinct date) = 2,

firstsortedvalue(Distinct {<price_pcs-={'0'}>}   

aggr(min(price_pcs), date, customer, item),-date, 2),

firstsortedvalue(Distinct {<price_pcs-={'0'}>}   

aggr(min(price_pcs), date, customer, item),-date, 3)))

[jonathandienst]

You might be better off using Rank() to select the Top N values dynamically. I am not sure what you were getting/attempting with the expression you posted. There are quite a few articles on using Rank() to get the sort of result you are looking for.

[Anonymous]

Hi I discussed with Robert yet in the German forum about this topic.

I also don't understand why

firstsortedvalue(

xxxxx,

yyyyy,

zzzzz)

doesn't work, when  zzzzz contains aggregation functions...for me it's a bug.

Regards

Robin

[sunny_talwar]

Might be a bug or may be not... but there are a lot of similar functions where the parameters are not evaluated based on the chart dimension but in totality. To me this seems to be by design, but why do it, may be to get better performance? or something else.... Work around would be to use Pick(Match...)) or using if

[Anonymous]

it looks better

[robbybalboa]

Good solution provided by Marcus Sommer.

See link

Re: Niedrigster Wert aus bestimmten Rechnungen, Null Werte firstsortedvalue