Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
I have a table like below which has date campaign clicks and hour.
Every hour when data gets pulled from DB, its a cumulative hourly pull
Eg: when data is fetched at 3am, it fetches from 12am -3am & when data is fetched at 4am, it fetches from 12am -4am
So my requirement is to get a hourly breakdown for the clicks for each hour as shown in Output
How to achieve this in back end script?
Output | ||||||
Date | campaign | Clicks | Hour | Hour Breakdown | Clicks | |
24/11/2020 | A | 100 | 1 | 1 | 100 | |
24/11/2020 | A | 250 | 2 | 2 | 150 | |
24/11/2020 | A | 400 | 3 | 3 | 150 | |
24/11/2020 | A | 700 | 4 | 4 | 300 |
@sanjujeeboy try below
Data:
LOAD Date
campaign
Clicks
Hour
FROM Table;
Final:
load *,
if(campaign<>previous(campaign) or Date<>previous(Date),Clicks,Clicks-previous(Clicks)) as Clicks_new
resident Data
order by campaign,Date,Hour;
drop table Data;
try
=sum(Clicks)-rangesum(above(sum(Clicks)))
Hi Edwin ,
Can you tell me how to achieve this in back end script?
@sanjujeeboy try below
Data:
LOAD Date
campaign
Clicks
Hour
FROM Table;
Final:
load *,
if(campaign<>previous(campaign),Clicks,Clicks-previous(Clicks)) as Clicks_new
resident Data
order by campaign,Date,Hour;
drop table Data;
just add it as an expression in your straight table.
Kush has a better solution below where the measure you are looking for is computed in the script thus making your UI run faster
here is a different option that uses table operations.
do a cartesian join and retain only the 1st hour of the day + current and prior hours;
create an expression for the actual click: if Hour =1 -> first hour, use Clicks; if not, use Clicks-PriorClick.
the assumption is that there is no relationship between prior day Hour 24 and current day hour 1. if there is, the date needs to be expanded with the hour and do a similar join.
@Kushal_Chawda Hi Kush, the logic seems to work , but only at one point i see difference is for 25th july, 0 hour its taking difference from previous days 23rd hour. but my requirement is for 0 hour it should remain same ie 1139
how to achieve this? i have attached excel for reference.
Thanks
try this corrected for 0 hr
@sanjujeeboy try below
Data:
LOAD Date
campaign
Clicks
Hour
FROM Table;
Final:
load *,
if(campaign<>previous(campaign) or Date<>previous(Date),Clicks,Clicks-previous(Clicks)) as Clicks_new
resident Data
order by campaign,Date,Hour;
drop table Data;
@Kushal_Chawda Hi Kush, Apart from date campaign clicks hour, i have 3 more dimensions : keyword ,adgroup, adgroup ID
these also have to added in if condition? and order by statement?