Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
First I have joined a QVW to show you what I want to do.
I have several lines with:
- Code_article = Item number
- Code_depot = depository
- Num_Contrat = contract number
Others are less important for the understanding.
Code_Article | Code_Depot | Num_Contrat | Palier | Serie_economique |
1 | 1 | 1 | 1 | 1 |
1 | 1 | 1 | 2 | 0 |
1 | 2 | 1 | 10 | 1 |
1 | 2 | 3 | 100 | 1 |
2 | 1 | 2 | 1000 | 1 |
2 | 1 | 2 | 2000 | 0 |
4 | 4 | 4 | 3000 | 0 |
4 | 4 | 4 | 4444 | 1 |
4 | 4 | 4 | 5555 | 0 |
4 | 4 | 4 | 6666 | 0 |
For each combination of Code_article , Code_depot , Num_Contrat I have one line with "Serie_economique" = 1.
I want one line for each so:
Code_Article | Code_Depot | Num_Contrat | Filtre | Palier |
1 | 1 | 1 | OK | 1 |
1 | 2 | 1 | OK | 10 |
1 | 2 | 3 | OK | 100 |
2 | 1 | 2 | OK | 1000 |
4 | 4 | 4 | OK | 4444 |
I have made a filter to show only these lines.
It is OK, I have these lines
But I want more
I would like to know if another line exists with "Serie_economique" = 0 and more, the min value with "Serie_economique" = 0 which is superior to the one with "Serie_economique" = 1 ...
Something like that:
Code_Article | Code_Depot | Num_Contrat | Filtre | Palier | Test |
1 | 1 | 1 | OK | 1 | 2 |
1 | 2 | 1 | OK | 10 | |
1 | 2 | 3 | OK | 100 | |
2 | 1 | 2 | OK | 1000 | 2000 |
4 | 4 | 4 | OK | 4444 | 5555 |
I need help for column "Test" in this table.
You can see my tests in the QVW but I can not find the good expression. I only tried to find teh max value for the combination of Code_article , Code_depot , Num_Contrat before doing more complex case.
But I always have:
- the same value as "Palier"
- the max value of all the table
I can not do it in my script because my real need is much more complicated
With my example, I want to understand how it is working in a "simple" qvw.
Help me please to understand why and how to solve my problem.
Thank you,
Willy
Yep but it is not the result I need
I want the min value of Palier as you did, BUT which is superior to the line with Serie_economique = 1
In general yes, but not if you reference an expression that is an aggregation function (min() ) from within another aggregation function.
Oh, yes, I've missed that requirement, sorry.
You can create a flag in your script, that indicates the required Palier value, or use a bit complicated expression in your chart.
Both methods shown in attached sample file.
Thank you swuehl .
I tried without min in the first expression, but it is not working
Palier1=Palier
And
Test:
=min(
If(
Serie_economique=0
And Palier >= Palier1
, Palier
)
)
Hi Willy,
why column Test is empty ?
Thank you for all your answers
I can not do it in my script because may real need is much more complicated.
Your expression seems to work, I will try it in my real example.
Regards,
Willy
Hi Antonio,
I have this when I open you QVW:
????!!!!
I try to attach again
Same thing
But swuehl answered m.
Thank you for your time.
Willy