Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

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

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

6 Replies
Highlighted

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

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)))

Highlighted
MVP
MVP

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

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Anonymous
Not applicable

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

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

Highlighted

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

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

Highlighted
Anonymous
Not applicable

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

it looks better

Highlighted
Contributor III
Contributor III

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

Good solution provided by Marcus Sommer.

See link

Re: Niedrigster Wert aus bestimmten Rechnungen, Null Werte firstsortedvalue