Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Would like to do a simple VLookup in Qlik. Situation:
Please see 3 Tables below. Goal: Get Column "Count Weighted". Tried with this formular but get empty values:
Count({$<Status={2}>} RoleName) * Factor_CarType
Thanks for any help
CarType | |||
ID_Type | Name | Factor_CarType | |
1 | BMR | 12.5 | |
2 | Tyote | 10.0 | |
Quantity | |||
ID_Type | RoleName | Count | |
1 | A | 10 | |
1 | B | 500 | |
2 | A | 50 | |
2 | B | 600 | |
Pivotable | |||
Status | RoleName | Count | Count Weighted |
finished | A | 60 | 625 |
B | 1100 | 12250 |
@edwin: Your last expression worked. Thanks.
Next step: Would like to show a Chart History. The Aggr() Function is good for one aggregated figure, not for a time series. How should Implemed that? Can also open a new thread?
if you want the aggregation by Date and Rolename, just add the date to the aggr:
=aggr(NODISTINCT sum(aggr( count(RoleName), RoleName, ID_Type, Dt)*Factor_CarType), RoleName, Dt)
Does not work. Chart is empty
Thanks Edwin.
With you App, did you see the problem below? Have some gaps and believe dont need a muliplicator with the factor anymore. A Sum of the Factor should do it.
Believe it is not neccsary anymore to
This is the solution:
Sum({$<RoleName={2}>} Factor)
there are two solutions embedded in the QVW i sent you - one copies the Factor to the main fact table so you dont need to look it up. this was what i suggested the first time so you just need to sum the factor as your transaction records have a unit count = 1 (you dont need the count you just sum the Factor).
2nd solution is if you cant add Factor into your fact table, use the expression with the AGGR function. your choice and it appears youve made it.