topic Re: Expression Min value based on key ordering in QlikView

Expression Min value based on key ordering

Wed, 25 Nov 2020 16:16:04 GMT

Dear all,

I am requesting a little helphand regarding my suppliers prices base

I have created the expression [prix_€] based on all prices and currency rates (monthly fixed for the plant selected or with variable cursors) = Ok

I am ordering expression [var_pour_tri] wich is the result of your selection of 1 of the 4 ordering keys selection (Article+qté=[Clé] - article - article part number - supplier proposed part number) = Ok

Everything is working werll (here is after a print screen if necessary)

I 'd like to report the minimum € price [Min_Prix_€] based on the oredring key selected

It seems that the aggr function is not available with expresions ? min ( aggr ( min ( [PU_€] ) , [Clé] ) )

Unsuccessfull with the first sorted value too FirstSortedValue([PU_€],[Variable_de_Tri])

I tried with the macro module but no success

SUB Report_Prix_Euro_Mini_Article_Qte
SET Table = ActiveDocument.GetSheetObject("CH17")
set Cle = ActiveDocument.Variables("Clé")
set MiniPrixEuro = ActiveDocument.Variables("Mini_Prix_€")
set PuEuro = ActiveDocument.Variables("PU_€")

for i=1 to Table.GetRowCount
if Cle(i) =Cle(i-1) then
MinPrixEuro(i)=MinPrixEuro(i-1)
else
MinPrixEuro(i)=PuEuro(i)
end if
next
END Sub

If someone can help me, thanks in advance

Regards

Ted

Re: Expression Min value based on key ordering

Wed, 18 Jan 2017 09:03:03 GMT

Here is a full screenshot

The min_Prix_€ is the last remaining calculted expression to finalize my base. I will be able after to measure the gap for each line (vlaued and %) face to the best one

Re: Expression Min value based on key ordering

tresB — Wed, 18 Jan 2017 09:19:45 GMT

What is your chart dimension? How do you wish to see the result? Could you post a sample qvw to work upon?

Re: Expression Min value based on key ordering

Wed, 18 Jan 2017 09:40:49 GMT

Thanks for your first help

result has to be a numeric with 5 digits as the min_pu_€

How to post my qvw ?

Re: Expression Min value based on key ordering

Wed, 18 Jan 2017 10:41:50 GMT

As I don't konw how to post my qvw, here are all my expressions:

to explain my base

converted prices in case of usage of cursors

**************variable PU with cursor **************************

[PU € Var] =
if ( (Devise) = 'USD', (PU_Devise_Détaxé_Rendu / (Var_Taux_USD) ),
if ( (Devise) = 'JPY', (PU_Devise_Détaxé_Rendu/ (Var_Taux_JPY) ),
if ( (Devise) = 'TND', (PU_Devise_Détaxé_Rendu / (Var_Taux_TND) ),
if ( (Devise) = 'GBP', (PU_Devise_Détaxé_Rendu / (Var_Taux_GBP) ),
if ( (Devise) = 'EUR', (PU_Devise_Détaxé_Rendu)
) ) ) ) )

converted prices with the month rates

**************fixed PU with the fixed rates of the month **************************

[PU € fixe]=
if ( (Devise) = 'USD', ( PU_Devise_Détaxé_Rendu / (Taux_fixe_USD) ),
if ( (Devise) = 'JPY', ( PU_Devise_Détaxé_Rendu / (Taux_fixe_JPY) ),
if ( (Devise) = 'TND', ( PU_Devise_Détaxé_Rendu / (Taux_fixe_TND) ),
if ( (Devise) = 'GBP', ( PU_Devise_Détaxé_Rendu / (Taux_fixe_GBP) ),
if ( (Devise) = 'EUR', PU_Devise_Détaxé_Rendu)
) ) ) )

if you don't manage cursors also converted prices with the month rates are showed

**************What the one to show**************************

[PU €]==if ( [PU € var] <> [PU € fixe], [PU € var], [PU € fixe] )

With the button selected (5 cases) the [Var_de_Tri] is affected to order the list

************** Var_de_Tri] is the expression chosen **********

[Var_de_Tri]=
if([Var_Pour_Tri]='A',[Clé],
if([Var_Pour_Tri]='B',[Code de l'article],
if([Var_Pour_Tri]='C',[Réference source],
if([Var_Pour_Tri]='D',[Réference proposée],
if([Var_Pour_Tri]='E',[Sanity_check],
)))))

I also need to report the [PU_€] for the [Var_de_Tri] in front of each line

May be this description hlep