9 Replies Latest reply: Aug 14, 2017 7:04 AM by Nick Jose

# How to use this expression containing variable in Qlik Sense frontend?

Hi,

Problem 1: I am trying to use the below expression in QlikSense Table but its giving a '-'. However the expressions when used individually are working fine in the previous columns of the same table.

Sum({1<Category={'\$(Variable1)'},[Flag]={'1'}>}Quantity) /

Sum({1<Category={'\$(Variable1)'},[Flag]={'2'}>}Quantity)

I need to find the ratio between the two expressions by dividing first with the other.

The variable is used to take the category associated with a selected field.

Problem 2: I have calculated the ranks of a field using this expression in a table:

(Sum({<[Field_Type]={"=Rank(Sum(Quantity)) <=5"},[Flag]={'1'}>}Quantity))

This gives the rank wise Field types. Now this quantity exists in three different tiers viz. Tier I, Tier II & Tier III.

Now along with the rank I need to show its breakup in different tiers.

a. I am not able to find the right expression that will work in the same table along side the main expression where Ranking is calculated.

b. The ranking also doesn't stop at 5 despite using <=5 in the expression, it is rather showing all the ranks. ( Need just Top 5).

A quick help would be highly appreciated.

Thanks

Nick

• ###### Re: How to use this expression containing variable in Qlik Sense frontend?

Just some thoughts:

As to problem 1:

It is hard to see that is should be a problem getting the ratio as long as the last expression calculates to a zero and you will get a zero division problem. However the Variable1 needs to have a single category and how do you make sure that a single category always is selected?

As to problem 2:

This part of your set expression: [Field_Type]={"=Rank(Sum(Quantity)) <=5"}

The rank will get all [Field_Type] values selected that has at least a rank less or equal to 5. It does not filter away anything that is not top 5. To get this type of expression to work you would often need the [Field_Type] - that is the left hand field - to be a key so it can uniquely identify and select values that should be included in the aggregation.

• ###### Re: How to use this expression containing variable in Qlik Sense frontend?

Hi Petter,

For problem 1: Now I have added a condition that the denominator should not be equal to zero, even then its not working.

I tried Column 1 / Column 2 which is also not working.

For the category part: This report is supposed to work when one 'Model' is selected which exists only in a single category which means 'Variable1' has a single category for the both the expressions.

Can you also suggest something for problem 2 part a? which is to show the rank wise field's distribution in three different tiers.

Thanks

• ###### Re: How to use this expression containing variable in Qlik Sense frontend?

Hi,

I have resolved the Problem 1.

Need help with the Problem 2 i.e:

I need to find Top 5 'Field A' with Showing their contribution in 'Field B' , 'Field C' and 'Field D'.

Also the ranking should appear as a % (not a quantity).

Data:

 Field A Quantity Field B Field C Field D x 300 100 150 50 y 100 50 30 20 z 350 150 90 110 x1 300 220 50 30 y1 480 100 80 300 z1 490 170 300 20 x2 350 100 200 50 y2 470 200 100 170 z2 920 300 300 320 Total 3760

Required Output:

 Rank Field A % Contribution Field B Field C Field D 1 z2 24.47 32.61 32.61 34.78 2 z1 13.03 34.69 61.22 4.08 3 y1 12.77 20.83 16.67 62.50 4 y2 12.50 42.55 21.28 36.17 5 x2 9.31 28.57 57.14 14.29

Ranking is based on Highest Quantity of Field A.

% Contribution = Quantity/Total Quantity * 100

Field B, Field C & Field D = Field A Split into Field B, C & D respectively as a percentage.

Note: I need to use a transaction flag in this expression. Assume: 'Flag A'

Any help would highly appreciated.

Thanks,

Nick

• ###### Re: How to use this expression containing variable in Qlik Sense frontend?

Dear Nick,

By using Rank and Aggregation functions you can you achieve the result.

kindly find the attached application. ! !

updated Attachment.

• ###### Re: How to use this expression containing variable in Qlik Sense frontend?

Thanks a lot Mohammed Mukram. This is great.

• ###### Re: How to use this expression containing variable in Qlik Sense frontend?

Hi Nick,

I have used Calculated Dimension like this

=Aggr(If(Rank(Sum(Quantity),4,1) <= 5,[Field A]),[Field A])

Expression :

Sum(Quantity)/Sum(TOTAL Quantity)

Sum([Field B])/Sum(Quantity)  ..........

Regards,

Antonio

• ###### Re: How to use this expression containing variable in Qlik Sense frontend?

Thanks a lot Antonio. This is great.

• ###### Re: How to use this expression containing variable in Qlik Sense frontend?

I have used dimension limit feature of straight table.

Dimension:

Field A

Expressions:

1)

=rank(sum(Quantity)/sum(TOTAL Quantity),0,1)

2)

=sum(Quantity)/sum(TOTAL Quantity)

3)

=sum([Field B])/sum(Quantity)

4)

=sum([Field C])/sum(Quantity)

5)

=sum([Field D])/sum(Quantity)

• ###### Re: How to use this expression containing variable in Qlik Sense frontend?

Thanks a lot Kushal. You are always helpful.