# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
New Contributor

## How to use IF function in the same column

Dear community,

We have sensors at productionlines which measure the number of products passing by. Every noon they reset the total number for the day. Every minute it gives the new total for the day so far i.e. 14:01:00 = 100654, 14:02:00 = 100676. Therefore I can't use the SUM-function in analysis and I'm using the MAX-function. The problem with this, I can't zoom in to hours or minutes and can't zoom out for weeks, months and further (i.e. It will give the MAX value for the week, when it has already been reset six times at least)

Can I use a combined MAX and SUM function in the Analysis part?  Or should I make a new column in the table which calculates the difference between two measures from the sensors? (in my mind this is the simplest way) I thought about the IF function like MS Excel:

=IF(condition;if_value_is_true;if_value_is_false)

=IF(measureB1>measureB2;0;measureB2-measureB1)

This leaves the negative values out (when the systems resets).

Which is the simplest way to do a correct measure and how to calculate it?

Labels (1)
• ### IF & OR

2 Solutions

Accepted Solutions
Valued Contributor III

## Re: How to use IF function in the same column

Have you considered calculating relative amounts in your data. for every row calculate the delta from the previous row within the reset cycle.

You can get the idea by examening the script below.

```//Assuming dates in format 'YYYY-MM-DD'RawData:
LOAD *,    DayName(Date+Time,-1,0.5) as LogDate , //The file resets every day at noon   frac( Time + 0.5 ) as LogRelativeTime  //Time passed from noon
Inline [
Date, Time, Measure
2019-04-15, 14:02:00, 100676
2019-04-15, 14:01:00, 100654
2019-04-15, 11:02:00, 200676
2019-04-15, 11:01:00, 200654
2019-04-14, 14:02:00, 100677
2019-04-14, 14:01:00, 100653
2019-04-14, 12:00:00, 3
2019-04-14, 11:02:00, 200677
2019-04-14, 11:01:00, 200653
2019-04-13, 14:02:00, 100678
2019-04-13, 14:01:00, 100656
];

LogDate,
Date,
Time,
Measure as [Day total acc Measure], //Your old value
if(peek('LogDate')=LogDate, Measure - Peek('Day total acc Measure'), Measure) as Measure
Resident
RawData
ORDER BY LogDate asc, LogRelativeTime asc;
Drop table RawData;
exit script```
Please ekskuse my Norglish and Swenglish typos.
New Contributor

## Re: How to use IF function in the same column

I get an error while trying to change "measure" to my own column. I want to do it for all eight measures (Dutch: 'metingen'). Have I made an mistake? I've also added the coding for the original table.

6 Replies
Valued Contributor III

## Re: How to use IF function in the same column

Have you considered calculating relative amounts in your data. for every row calculate the delta from the previous row within the reset cycle.

You can get the idea by examening the script below.

```//Assuming dates in format 'YYYY-MM-DD'RawData:
LOAD *,    DayName(Date+Time,-1,0.5) as LogDate , //The file resets every day at noon   frac( Time + 0.5 ) as LogRelativeTime  //Time passed from noon
Inline [
Date, Time, Measure
2019-04-15, 14:02:00, 100676
2019-04-15, 14:01:00, 100654
2019-04-15, 11:02:00, 200676
2019-04-15, 11:01:00, 200654
2019-04-14, 14:02:00, 100677
2019-04-14, 14:01:00, 100653
2019-04-14, 12:00:00, 3
2019-04-14, 11:02:00, 200677
2019-04-14, 11:01:00, 200653
2019-04-13, 14:02:00, 100678
2019-04-13, 14:01:00, 100656
];

LogDate,
Date,
Time,
Measure as [Day total acc Measure], //Your old value
if(peek('LogDate')=LogDate, Measure - Peek('Day total acc Measure'), Measure) as Measure
Resident
RawData
ORDER BY LogDate asc, LogRelativeTime asc;
Drop table RawData;
exit script```
Please ekskuse my Norglish and Swenglish typos.
New Contributor

## Re: How to use IF function in the same column

I get an error while trying to change "measure" to my own column. I want to do it for all eight measures (Dutch: 'metingen'). Have I made an mistake? I've also added the coding for the original table.

New Contributor

## Re: How to use IF function in the same column

Valued Contributor III

## Re: How to use IF function in the same column

You are missing a comma (,)  after your [Datum-Tijd]

Please ekskuse my Norglish and Swenglish typos.
New Contributor

## Re: How to use IF function in the same column

(Datum-Tijd translates to Date-Time). Almost there! The function "drop table" doesn't work. Do you know where I went wrong this time?

New Contributor

## Re: How to use IF function in the same column

I've solved it with the IF & previous combination and Dayname & frac seperately. Try these. Thanks.
Community Browser