17 Replies Latest reply: Jul 20, 2016 8:54 AM by Joanna Seldon

# count if , and distinct times after target is met

Hi

in regards to temperatures 37.5

I wish to count only, after a certain number temperature target has been reached '37.5'. and only the distinct times temperature dips below 37.5

test 1

TestNo     Temp

1               37.5

2               37.5

3               37.5        = 0 dips

Test 2

TestNo     Temp

1               36.5

2               37.5    (Target temp has been reached)

3               37.5

4               36.5    (dip has occurred)

5               36.5

6               37.5

7               37.5

8               37.5   =    1 dip

• ###### Re: count if , and distinct times after target is met

what is dips?

• ###### Re: count if , and distinct times after target is met

Hi

a dip in temperature is, when a temperature goes below 37.5

• ###### Re: count if , and distinct times after target is met

So can I assume that certain number is 3 here..

• ###### Re: count if , and distinct times after target is met

Hi

no 1,

this is because temperature test no has to reach 37.5 first , then count distinct number dips in temperature there after  which is 1

• ###### Re: count if , and distinct times after target is met

Hi,

Have you checked my reply ? with attachment

"qlikview coummity 27.06.2017 dip-in_tempreature EDITED.qvw"?

• ###### Re: count if , and distinct times after target is met

Hi

It is like this ?

I have attached a straight table with some conditional colors .

Edited : Attachment with pivot :  working fine

• ###### Re: count if , and distinct times after target is met

Hiya

the pivot is exactly right, but I am using qlik sense. I need to add this calculation to a bar chart. how many dips on a date

• ###### Re: count if , and distinct times after target is met

Hi

Please find the qvw as per requirement in bar chart.

• ###### Re: count if , and distinct times after target is met

Hi

IF(Previous(Temp)-Temp<'0','Dip','Normal') as  Temp_variation

the first issue i have is data is being loaded from a sql database ..

I have added this as an if statement, it seems to only work if you place >'0' to a <'0'

the issue I am having is when it sees the same value ,

i keep getting this for an room id

RoomID is in same table..

RoomID    TestNo     Temp   Temp_Variation                 should be

1                1              37.5        NORMAL

1                2              34.8        NORMAL                        DIP

1                3              37.5        NORMAL

1                4              37.5        NORMAL

1                5              37           NORMAL

1                6              37           NORMAL

1                7              37.5        NORMAL

1                8              37.5        DIP

IF(Previous(Temp)-Temp>'0','Dip','Normal') as  Temp_variation

IF I place the > in the correct way as in your script I get

RoomID    TestNo     Temp   Temp_Variation                 should be

1                1              37.5        NORMAL

1                2              34.8        NORMAL                        DIP

1                3              37.5        NORMAL

1                4              37.5        NORMAL

1                5              37           NORMAL

1                6              37           NORMAL

1                7              37.5        NORMAL

1                8              37.5        NORMAL

• ###### Re: count if , and distinct times after target is met

Hi

IF(Previous(Temp)-Temp>0,'dip','Normal') as  Temp_variation

this gives  the value if temperature in the series decreased

eg:

37.5

37.5

36.5

36.5

..

...

...

...

here it will count as one 'dip'

37.5

37.5

36.5

36.5

36.5

36.5

35.5

..

...

..

here it will count as two dips

Note :  in the properties of PIVOT TABLE

sort :

Temp : Numeric value

emp_Variation : state :Asecnding

• ###### Re: count if , and distinct times after target is met

Hi

I have tried to use your script and amend it and still no luck. please see below

SOURCE:

"TestNo",

"Temp";

SQL SELECT "RoomID",

"TestNo",

"Temp"
FROM dbo.RoomTemp;

NoConcatenate
TEMP1:
IF(Previous(Temp)-Temp<0,'dip','Normal') as  Temp_variation
Resident SOURCE Order by TestNo asc,  Temp asc  , "RoomID" asc ;
drop table SOURCE;

for temp variation am I supposed to sort by expression on the table?

• ###### Re: count if , and distinct times after target is met

Hi

please don't do any sorting in  EDITOR :

SOURCE:

"TestNo",

"Temp";

SQL SELECT "RoomID",

"TestNo",

"Temp"
FROM dbo.RoomTemp;

NoConcatenate
TEMP1:
IF(Previous(Temp)-Temp<0,'dip','Normal') as  Temp_variation
Resident SOURCE  ;
drop table SOURCE;

in the properties of PIVOT TABLE..............>Sort................>

Temp : Numeric value

emp_Variation : state :Asecnding

• ###### Re: count if , and distinct times after target is met

Hi

I have tried the above it did not work

here is the result:

RoomID    TestNo     Temp   Temp_Variation                 should be

1                1              37.5        Dip                                  NORMAL

1                2              34.8        Dip                                  DIP

1                3              37.5        NORMAL                        NORMAL

1                4              37.5        NORMAL                        NORMAL

1                5              37           NORMAL                        NORMAL

1                6              37           NORMAL                        NORMAL

1                7              37.5        NORMAL                        NORMAL

1                8              37.5        DIP                                  NORMAL

I am using qlik sense..and a straight table. I will then be using a Kpi to count the number of dips.

sorry

I did try putting the

• ###### Re: count if , and distinct times after target is met

Hi

I am understand ,why it is not working for you  .

FYI

I have done this in QLIK sense as well .

• ###### Re: count if , and distinct times after target is met

Hi

I don't know if you can help

I am now using your formulary expression

IF(Previous(Temp)-Temp>0,'dip','Normal') as  Temp_variation

this works great...I got another  issue  please ...how can I amend the above to only show a normal. if temp is 35 and above?

as this record

RoomID    TestNo     Temp   Temp_Variation              Hope to be

1                1              37        NORMAL                        NORMAL

1                2              36        DIP                                 NORMAL

1                3              35        DIP                                 NORMAL

1                4              36        NORMAL                        NORMAL

1                5              35        DIP                                 NORMAL

1                6              35        NORMAL                        NORMAL

1                7              35        NORMAL                        NORMAL

• ###### Re: count if , and distinct times after target is met

Hi

use the following in another expression :

IF(Temp>=35,'Normal',(IF(Previous(Temp)-Temp>0,'dip','Normal')))as Tempvarianceabove_35

....

SOURCE:

[TestNo, Temp

1, 36.5

2, 37.5

3, 37.5

4, 36.5

5, 36.5

6, 37.5

7, 37.5

8, 37.5

9,  34.9

10, 34.8

11, 35

12, 35.1

13, 36.7

14, 35.00

15, 35.12

16, 34.00

];

NoConcatenate

TEMP1:

IF(Previous(Temp)-Temp>0,'dip','Normal') as  Temp_variation,

IF(Temp>=35,'Normal',(IF(Previous(Temp)-Temp>0,'dip','Normal')))as Tempvarianceabove_35

Resident SOURCE Order by TestNo asc,Temp asc ;

drop table SOURCE;

• ###### Re: count if , and distinct times after target is met

fab xxx thank you