Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Dear Nick,
By using Rank and Aggregation functions you can you achieve the result.
kindly find the attached application. ! !
updated Attachment.
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.
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
petter-s kush141087 serj_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 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
Dear Nick,
By using Rank and Aggregation functions you can you achieve the result.
kindly find the attached application. ! !
updated Attachment.
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
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)
Thanks a lot Mohammed Mukram. This is great.
Thanks a lot Antonio. This is great.
Thanks a lot Kushal. You are always helpful.