Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nickjose7
Creator
Creator

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

kush141087serj_shu

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

1 Solution

Accepted Solutions
mdmukramali
Specialist III
Specialist III

Dear Nick,

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

kindly find the attached application. ! !

updated Attachment.

271092.PNG

View solution in original post

9 Replies
petter
Partner - Champion III
Partner - Champion III

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.

nickjose7
Creator
Creator
Author

Hi Petter,

Thanks for your response.

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

nickjose7
Creator
Creator
Author

petter-skush141087serj_shu

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 AQuantityField BField CField D
x30010015050
y100503020
z35015090110
x13002205030
y148010080300
z149017030020
x235010020050
y2470200100170
z2920300300320
Total3760

Required Output:

   

RankField A% ContributionField BField CField D
1z224.4732.6132.6134.78
2z113.0334.6961.224.08
3y112.7720.8316.6762.50
4y212.5042.5521.2836.17
5x29.3128.5757.1414.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

mdmukramali
Specialist III
Specialist III

Dear Nick,

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

kindly find the attached application. ! !

updated Attachment.

271092.PNG

antoniotiman
Master III
Master III

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

Kushal_Chawda

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)

nickjose7
Creator
Creator
Author

Thanks a lot Mohammed Mukram. This is great.

nickjose7
Creator
Creator
Author

Thanks a lot Antonio. This is great.

nickjose7
Creator
Creator
Author

Thanks a lot Kushal. You are always helpful.