Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
rthanga2
New 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
MVP & Luminary
MVP & Luminary

Re: Please help with set expression for the charting in Qlik

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