hourly breakdown of data

[sanjujeeboy]

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

@Kushal_Chawda @sunny_talwar @Taoufiq_Zarra 

[edwin]

try 

=sum(Clicks)-rangesum(above(sum(Clicks)))

[sanjujeeboy]

Hi Edwin ,

Can you tell me how to achieve this in back end script?

[Kushal_Chawda]

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

[edwin]

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

[edwin]

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.

[sanjujeeboy]

@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

[edwin]

try this corrected for 0 hr

[Kushal_Chawda · Accepted solution]

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

[sanjujeeboy]

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