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
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.
Maybe like attached?
In test can u try this:
=aggr(Code_Article,Code_Depot, Num_Contrat,Palier)
Hi Willy,
see attachment.
Regards,
Antonio
Thank you for the answer.
The aggr give me the number of lines corresponding to the combination of Code_article , Code_depot , Num_Contrat
Hello,
thank you for the answer.
I have changed the first column with:
min(If(Serie_economique=1, Palier)) instead of min(Palier)) , beacause I want the minimum with Serie_economique=1 --> It works (but I did the same thing with my filter)
And named it "Palier1"
For the second column, I tried this:
=if(
count(Distinct Palier)>1
, Min(
If(Serie_economique=0 and Palier>[Palier1]
, Palier
)
)
)
But it is not working
Can I use an expression in another one ?
And if YES, is it not working because of the Min function ?
Regards,
Willy
What is [Palier1]? A reference to another expression (the one you mentioned first)? That won't work.
But I don't think you need this second AND condition, try
=if(
count(Distinct Palier)>1
, Min(
If(Serie_economique=0
, Palier
)
)
)
You can simplify your requested expressions by using the three dimensions and two expressions as shown here:
Code_Article | Code_Depot | Num_Contrat | min({<Serie_economique = {1} >}Palier) | min({<Serie_economique = {0} >}Palier) |
---|---|---|---|---|
1 | 1 | 1 | 1 | 2 |
1 | 2 | 1 | 10 | |
1 | 2 | 3 | 100 | |
2 | 1 | 2 | 1000 | 2000 |
4 | 4 | 4 | 4444 | 3000 |
Hello Antonio,
thank you for your proposal.
Palier column is OK, you gave me another solution to find it.
But my "Test" column is empty in your qvw And this is the one I need.
Thank you, Willy
Yes [Palier1] is the first expression: min(If(Serie_economique=1, Palier))
I can not use it in another expression ?