Solved: Show text as per Count Number - Qlik Community

Anonymous · ‎2018-02-20

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

Anonymous · ‎2018-02-23

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

sunny_talwar · ‎2018-02-20

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 · ‎2018-02-20

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

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

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 · ‎2018-02-21

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 · ‎2018-02-21

What is the significance of cumulative count?

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

Anonymous · ‎2018-02-21

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

Anonymous · ‎2018-02-22

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 · ‎2018-02-23

Will take a look at this today...

Anonymous · ‎2018-02-23

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 · ‎2018-02-28

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