Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I am trying to create an express as per the below table:
And looking the (1st Highest, 2nd Highest, 3rd Highest, 2nd Lowest and Lowest) in each block.
Block mean is (1 to 5) is Block1. (6 to 10) is block2, (11 to 15) is block3.
The variable has been created and expression is picking up the value. but its not providing the correct result.
Sample qvw and example are attached.
Can you please help me out for this?
Many Thanks. Regards,
Farrukh
Date | S1 | S2 | S3 | S4 | S5 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 |
15/06/2007 | 4 | 6 | 13 | 30 | 40 | 1H | 2H | 1H | ||||||||||||
22/06/2007 | 7 | 24 | 35 | 38 | 47 | 1H | ||||||||||||||
29/06/2007 | 4 | 36 | 38 | 42 | 43 | 1H | ||||||||||||||
06/07/2007 | 3 | 10 | 13 | 15 | 18 | 2L | 1H | 1H | 1L | |||||||||||
13/07/2007 | 8 | 15 | 18 | 20 | 34 | 1H | 1H | |||||||||||||
20/07/2007 | 5 | 9 | 27 | 35 | 46 | 1L | 1H | |||||||||||||
27/07/2007 | 4 | 5 | 23 | 33 | 44 | 1H | 2L | |||||||||||||
03/08/2007 | 5 | 13 | 25 | 28 | 37 | 1H | 1H |
Hi Sunny,
I am using the below expressions and its giving me the correct results.
In attached file I put these expressions in a variables.
and used the below expression.
=if(Num(Only({$<Period={">=$(=vPeriod)"}>} CDC1)) >0,
Pick(
Match(
Rangesum(Above(TOTAL Sum({$<Period={">=$(=vPeriod)"}>} CDC1),0,rowno(TOTAL))) , $(B1_v1H) , $(B1_v2H) , $(B1_v3H), $(B1_v2L) , $(B1_v1L) ),
'1H','2H','3H','2L','1L'))
I used the above logic provided by Settu, as well as you provide to someone in October 2015.
Link:
https://community.qlik.com/thread/186501
Please have a look at this, if you think there is another smarter way to do this then please let me know. Else its giving me the correct results as required.
Again thanks for your great help.
Kind regards,
Farrukh
I am not sure I understand, would you be able to elaborate on what the expected output needs to be and how you are getting it?
Hi Sunny,
Thanks for helping me out for this. I have attached an excel file where I have highlight the result. I have some random numbers. as shown below
Date | S1 | S2 | S3 | S4 | S5 |
15/06/2007 | 4 | 6 | 13 | 30 | 40 |
22/06/2007 | 7 | 24 | 35 | 38 | 47 |
Then I am putting these numbers into related cells as shown below (in example currently I have 1 to 15) (The total numbers are from 1 to 50)
N1 | N2 | N3 | N4 | N5 | N6 | N7 | N8 | N9 | N10 | N11 | N12 | N13 | N14 | N15 |
4 | 6 | 13 | ||||||||||||
7 |
then I am counting each cell value as below:
CDC1 | CDC2 | CDC3 | CDC4 | CDC5 | CDC6 | CDC7 | CDC8 | CDC9 | CDC10 | CDC11 | CDC12 | CDC13 | CDC14 | CDC15 |
1 | 1 | 1 | ||||||||||||
1 |
|
Then I am counting each cell as a CUMMULATIVE COUNT.
CDF1 | CDF2 | CDF3 | CDF4 | CDF5 | CDF6 | CDF7 | CDF8 | CDF9 | CDF10 | CDF11 | CDF12 | CDF13 | CDF14 | CDF15 |
0 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |
0 | 0 | 0 | 1 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |
AND THE FINAL RESULT WILL BE AS BELOW:
CDA1 | CDA2 | CDA3 | CDA4 | CDA5 | CDA6 | CDA7 | CDA8 | CDA9 | CDA10 | CDA11 | CDA12 | CDA13 | CDA14 | CDA15 |
1H | 1H | 1H | ||||||||||||
1H |
This window show each cell rank By Block (1 to 5 is block1, 6 to 10 is block2 and 11 to 15 is block3)
If Block
Please see the attached excel file for detail example
Kind regards,
Farrukh
Hi Sunny,
Have you seen the requirement or do I need to elaborate further? Please let me know if is there any further details required.
Kind regards,
Farrukh
What is the significance of cumulative count?
It seems that you assign 1H based on just the count?
Hi Sunny,
Cumulative count provide the (1st Highest , 2nd Highest, 3rd Highest, 2nd Lowest and Lowest) in a Horizontal Range of (CDF1 , CDF2 , CDF3 , CDF4 , CDF5) row.
Please see the further clarification as shown in below tables:
CDC - Current Digit Count
Current Digit Count | ||||
CDC1 | CDC2 | CDC3 | CDC4 | CDC5 |
1 | 1 |
CDF - Current Digit Cumulative or Column Cumulative Count
Cumulative Count | ||||
CDF1 | CDF2 | CDF3 | CDF4 | CDF5 |
4 | 1 | 5 | 3 | 2 |
CDA - Current Digit Rank based on Cumulative Count in the Range (See CDF Table above)
nth of Value | ||||
CDF1 | CDF2 | CDF3 | CDF4 | CDF5 |
2nd Highest | Lowest | 1st Highest | 3rd Highest | 2nd Lowest |
If CDC1 is > 0
Then
in the Range(CDF1 , CDF2 , CDF3 , CDF4 , CDF5) find the nth max.
Cumulative Count | ||||
CDA1 | CDA2 | CDA3 | CDA4 | CDA5 |
2H | 3H |
Now CDF1 is the 2nd Highest in a range of (CDF1 , CDF2 , CDF3 , CDF4 , CDF5).
And CDF4 is the 3rd Highest in a range of (CDF1 , CDF2 , CDF3 , CDF4 , CDF5).
The Result will be for CDA1 = 2H or 2nd Highest
And for CDA4 = 3H or 3rd Highest
Please let me know if you have any further clarification required.
Kind regards,
Farrukh
Good Morning Sunny,
Any luck on this? Please let me know if you still required further detail on this query.
Thanks & Regards,
Farrukh
Will take a look at this today...
Hi Sunny,
I am using the below expressions and its giving me the correct results.
In attached file I put these expressions in a variables.
and used the below expression.
=if(Num(Only({$<Period={">=$(=vPeriod)"}>} CDC1)) >0,
Pick(
Match(
Rangesum(Above(TOTAL Sum({$<Period={">=$(=vPeriod)"}>} CDC1),0,rowno(TOTAL))) , $(B1_v1H) , $(B1_v2H) , $(B1_v3H), $(B1_v2L) , $(B1_v1L) ),
'1H','2H','3H','2L','1L'))
I used the above logic provided by Settu, as well as you provide to someone in October 2015.
Link:
https://community.qlik.com/thread/186501
Please have a look at this, if you think there is another smarter way to do this then please let me know. Else its giving me the correct results as required.
Again thanks for your great help.
Kind regards,
Farrukh
When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others