Issue with null data

Dheeraj_1996
Dheeraj_1996 Member
in Beast Mode

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.

image.png

Tagged:

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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)

Answers

  • nmizzell
    nmizzell Contributor

    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
    Dheeraj_1996 Member

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

    image.png

  • MichelleH
    MichelleH Coach

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

  • Dheeraj_1996
    Dheeraj_1996 Member

    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.

    image.png

  • MichelleH
    MichelleH Coach

    @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
    Dheeraj_1996 Member

    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
    MichelleH Coach
    Answer ✓

    @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)

Categories