Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
rthanga2
Contributor
Contributor

Please help with set expression for the charting in Qlik

Hello All,

I have three tables i am importing as shown below, Table 1, Table 2 and Table 3. What i want to do is basically make KPI boxes with "Low", "Medium", "High" as shown in the guideline to show revenue in each of the category as shown in the Result below.

To explain Table 1 has Main Part, Components and Supplier for each component. Table 2 has revenue in Main part level i.e Revenue will be for A not for A1,A2 or A3 components. and Table 3 will have scores for suppliers in the range of 1 to 100.

So what the expression should do is for A since the suppliers are S1,S2 and S3 but since S3 has the highest score i.e 65 the revenue for A i.e $2000 will be associated with "High" and similarly B,C,D will be associated with "Medium" since there is only one supplier and and these suppliers fall under medium so the "Medium" KPI should sum to $5500.

 

Can you help me in writing a chart expression for this? Basically three KPI boxes for Low, Medium and High showing total revenue in that category.

 

Table:1

Main PartComponentsSupplier
AA1S1
AA2S2
AA3S3
BB1S2
CC1S4
DD1S4

 

Table 2

Main PartRevenue
A$2,000
B$1,000
C$3,000
D$1,500

 

Table 3

SupplierScore
S125
S245
S365
S450

 

Guideline:

Low<40
Medium40 to 60
High 60

 

Result:

Low: $0     Medium : $5500 ($1000 + $1500 +$2000)  High:$2000

 

Thanks,

Rakesh

1 Reply
Gysbert_Wassenaar

Here's an expression for the Medium category:
='Medium: ' & sum(If(aggr(max(Score),[Main Part])>=40 and aggr(max(Score),[Main Part])<60, aggr(sum(Revenue),[Main Part])))

talk is cheap, supply exceeds demand