Issue with null data

Dheeraj_1996

I am using the below beast modes calculations for a chart. where I am trying to find the no of records received today and yesterday. Data appends itself in the dataset date wise.

Eg: if yesterday 1 record received, today 2 records received. dataset will show 3 count for today.

Beastmode:

today:
case WHEN SUM(CASE WHEN event_date = CURDATE() then records END) - SUM(CASE WHEN event_date = CURDATE() -1 then records END) > 0
THEN SUM(CASE WHEN event_date = CURDATE() then records END) - SUM(CASE WHEN event_date = CURDATE() -1 then records END)
else 0 END

Yesterday:
case WHEN SUM(CASE WHEN event_date = CURDATE() -1 then records END) - SUM(CASE WHEN event_date = CURDATE() -2 then records END) > 0
THEN SUM(CASE WHEN event_date = CURDATE() -1 then records END) - SUM(CASE WHEN event_date = CURDATE() -2 then records END)
else 0 END

But in the table it is not populating correct value.

MichelleH

@Dheeraj_1996 If you only want to keep positive variances across multiple rows, then you'll need to restructure your data to include a column to include the previous days' records value. You can do this by using the LAG function in the MagicETL Rank & Window tile. Once you have added this column you can use a single aggregated case statement in your card like this:

Today

sum(case 
  when `event_date` = CURDATE() and `records` >= `previous_day_records`
    then `records` - `previous_day_records`
  else 0 
end)

Yesterday

sum(case 
  when `event_date` = CURDATE() - 1 and `records` >= `previous_day_records`
    then `records` - `previous_day_records`
  else 0 
end)

nmizzell

The summing is throwing things off.

This will get you the intended result:

Today

count(case when `event_date` = current_date() then `records` end)

Yesterday

count(case when `event_date` = date_sub(current_date(), interval 1 day) then `records` end)

Dheeraj_1996

No this does not help, My dataset has multiple error codes for every day and aggregated records in it.

MichelleH

@Dheeraj_1996 You should be able to use @nmizzell's logic if you change the aggregation from COUNT to SUM

Dheeraj_1996

No it isn't giving the desired result. it is populating all the records.. but i need the difference from today to yesterday. In my beastmode I am getting the correct figures, but the subtotal is incorrect. the subtotal when -ve is also converting to 0.

MichelleH

@Dheeraj_1996 I see. Based on the example in your original post it sounded like you wanted to simply add them. In that case, the issue has to do with the ELSE 0 your outer case statements. I'd suggest changing your formulas to be structured like this:

Today:

SUM(CASE WHEN event_date = CURDATE() then records else 0 END) 
- 
SUM(CASE WHEN event_date = CURDATE() - 1 then records else 0 END)

Yesterday:

SUM(CASE WHEN event_date = CURDATE() - 1 then records else 0 END) 
- 
SUM(CASE WHEN event_date = CURDATE() - 2 then records else 0 END)

Dheeraj_1996

yes, I am doing this. but this fetches -ve value when records decrease. I only want +ve entries so I am converting things to 0 when -ve. but then it is giving issue

MichelleH

@Dheeraj_1996 If you only want to keep positive variances across multiple rows, then you'll need to restructure your data to include a column to include the previous days' records value. You can do this by using the LAG function in the MagicETL Rank & Window tile. Once you have added this column you can use a single aggregated case statement in your card like this:

Today

sum(case 
  when `event_date` = CURDATE() and `records` >= `previous_day_records`
    then `records` - `previous_day_records`
  else 0 
end)

Yesterday

sum(case 
  when `event_date` = CURDATE() - 1 and `records` >= `previous_day_records`
    then `records` - `previous_day_records`
  else 0 
end)