Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Show text as per Count Number

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

   

DateS1S2S3S4S5123456789101112131415
15/06/200746133040 1H 2H 1H
22/06/2007724353847 1H
29/06/2007436384243 1H
06/07/2007310131518 2L 1H 1H 1L
13/07/2007815182034 1H 1H
20/07/200759273546 1L 1H
27/07/200745233344 1H2L
03/08/2007513252837 1H 1H
1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

View solution in original post

9 Replies
sunny_talwar

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?

Anonymous
Not applicable
Author

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

   

DateS1S2S3S4S5
15/06/200746133040
22/06/2007724353847

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)

   

N1N2N3N4N5N6N7N8N9N10N11N12N13N14N15
4 6 13
7

then I am counting each cell value as below:

   

CDC1CDC2CDC3CDC4CDC5CDC6CDC7CDC8CDC9CDC10CDC11CDC12CDC13CDC14CDC15
1 1 1
1

Then I am counting each cell as a CUMMULATIVE COUNT.

   

CDF1CDF2CDF3CDF4CDF5CDF6CDF7CDF8CDF9CDF10CDF11CDF12CDF13CDF14CDF15
000101000000100
000101100000100

AND THE FINAL RESULT WILL BE AS BELOW:

 

CDA1CDA2CDA3CDA4CDA5CDA6CDA7CDA8CDA9CDA10CDA11CDA12CDA13CDA14CDA15
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

Anonymous
Not applicable
Author

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

sunny_talwar

What is the significance of cumulative count?

Capture.PNG

It seems that you assign 1H based on just the count?

Anonymous
Not applicable
Author

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
CDC1CDC2CDC3CDC4CDC5
11

CDF - Current Digit Cumulative or Column Cumulative Count

Cumulative Count
CDF1CDF2CDF3CDF4CDF5
4153

2

CDA - Current Digit Rank based on Cumulative Count in the Range (See CDF Table above)

nth of Value
CDF1CDF2CDF3CDF4CDF5
2nd HighestLowest1st Highest3rd Highest2nd Lowest

If CDC1 is > 0

Then

in the Range(CDF1 , CDF2 , CDF3 , CDF4 , CDF5) find the nth max.

   

Cumulative Count
CDA1CDA2CDA3CDA4CDA5
2H3H

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

Anonymous
Not applicable
Author

Good Morning Sunny,

Any luck on this? Please let me know if you still required further detail on this query.

Thanks & Regards,

Farrukh

sunny_talwar

Will take a look at this today...

Anonymous
Not applicable
Author

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

pablolabbe
Luminary Alumni
Luminary Alumni

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