Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
joeybird
Creator III
Creator III

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

please help

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

please help

17 Replies
Anonymous
Not applicable

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 :

Testno: LOAD ORDER   original

Temp : Numeric value


emp_Variation : state :Asecnding





joeybird
Creator III
Creator III
Author

Hi

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

SOURCE:

LOAD "RoomID",

  "TestNo",

   "Temp";

SQL SELECT "RoomID",

  "TestNo",

   "Temp"
FROM dbo.RoomTemp;

NoConcatenate
TEMP1:
LOAD *,
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?

please help

Anonymous
Not applicable

Hi

please don't do any sorting in  EDITOR :

SOURCE:

LOAD "RoomID",

  "TestNo",

  "Temp";

SQL SELECT "RoomID",

  "TestNo",

  "Temp"
FROM dbo.RoomTemp;

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

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

Testno: LOAD ORDER   original

Temp : Numeric value

emp_Variation : state :Asecnding

joeybird
Creator III
Creator III
Author

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

please help

I did try putting the

Anonymous
Not applicable

Hi

please post your data (sample date) at least .

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

FYI

I have done this in QLIK sense as well .

.please check my QVF.

joeybird
Creator III
Creator III
Author

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

please help

Anonymous
Not applicable

Hi

use the following in another expression :

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

....

SOURCE:

LOAD * inline

[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:

LOAD *,

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;

joeybird
Creator III
Creator III
Author

fab xxx thank you