Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have 2 tables with One - Many Relation ship.
Table 1 - Contains ProjectID, one row per Project ID
Table 2 - Contains Project ID, Multiple rows per ProjectID with different values of completion % i.e a project ID will have multiple rows with different values of Completion %.
In a table in Qlik Sense, i create a column called 'Completed' to identify if a project is completed or not i.e. if for a Project ID, there is a row with completion % 100, it is complet.
=If(CompletionPercentage = 100, 'Y', 'N')
If i use the above expression i get 2 rows for each Project ID, one with value Y and one with value N.
I understand that each row in the second table is being checked.
Is there any other way to achieve this requirement?
Yours Truly,
S.Manikantan
Hi Manikantan
Max does work in expressions, but you need at least an Aggr expression to make it work:
If(Aggr(Max(CompletionPercentage),ProjectID)=100,'Y','N')
But the danger with Max might be when a project hits 100, but issues arise and it gets a setback to 97, it will still take the 100:
and thus show the project as completed as it might not be.
Don't know if this could be an issue or not.
Regards,
Tim P.
Hi Manikantan
Please try the following measure:
If(Sum(CompletionPercentage)/Count(ProjectID)<100,'N','Y')
(Dummy data on top, solution at the bottom)
Hope this helps.
Regards,
Tim P.
Optionally you can use
=If(Max(CompletionPercentage) = 100, 'Y', 'N')
to only check last available value (I assume there aren't any projects completed to more then 100% )
Hi Juraj, Max doesnt work in expression.
Thanks Tim. Logical solution!!
Hi Manikantan
Max does work in expressions, but you need at least an Aggr expression to make it work:
If(Aggr(Max(CompletionPercentage),ProjectID)=100,'Y','N')
But the danger with Max might be when a project hits 100, but issues arise and it gets a setback to 97, it will still take the 100:
and thus show the project as completed as it might not be.
Don't know if this could be an issue or not.
Regards,
Tim P.
Thanks Tim. Thats Perfect for my requirement.
Just to understand, i can use this kind of Aggregations only when i want to find a Max (or Min or Avg). Is it possible to check if atleast one row for the project has a completion percentage of exactly 50% (not greater or lesser)?
Yours Truly,
S.Manikantan
Just so I can understand your datamodel a bit better:
When you say a ProjectID can have multiple values of CompletionPercentage, is this a growing percentage?
E.g.:
ProjectID | CompletionPercentage |
---|---|
A | 0 |
A | 15 |
A | 27 |
A | 39 |
B | 0 |
B | 10 |
B | 34 |
B | 78 |
B | 100 |
So, Project A is at 39% completion and Project B is at 100% completion?
That's Correct Tim. So now using your suggestion above i can find if any project has crossed 50% completion using condition that Aggr(Max) > 50. But if i want to know if a project has touched exact 50% at some point of time. Max or Min etc cannnot be used. Is this feasible? (Just a hypothetical question to understand if we can accomplish this in a one-many relationship. Actually, my requirement has been met with the above answer).
I modified the dummy data from my previous answer to include a 50% at some point:
ProjectID | CompletionPercentage |
---|---|
A | 0 |
A | 15 |
A | 27 |
A | 39 |
B | 0 |
B | 10 |
B | 34 |
B | 50 |
B | 78 |
B | 100 |
Now if you'd use the following measure:
If(Aggr(Count({<CompletionPercentage={50}>}CompletionPercentage),ProjectID)>=1,'Y','N')
This will allow you to check if any ProjectID has at least hit exactly 50% one time. The measure allows for a ProjectID to hit 50% multiple times (e.g. you'd keep record of the CompletionPercentage by date and a Project would be stuck at 50% for multiple dates)