topic Re: hourly breakdown of data in QlikView

hourly breakdown of data

sanjujeeboy — Fri, 15 Nov 2024 23:33:42 GMT

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

Re: hourly breakdown of data

edwin — Wed, 25 Nov 2020 19:32:18 GMT

try

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

Re: hourly breakdown of data

sanjujeeboy — Wed, 25 Nov 2020 19:39:53 GMT

Hi Edwin ,

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

Re: hourly breakdown of data

Kushal_Chawda — Wed, 25 Nov 2020 22:05:29 GMT

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

Re: hourly breakdown of data

edwin — Wed, 25 Nov 2020 23:15:48 GMT

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

Re: hourly breakdown of data

edwin — Wed, 25 Nov 2020 23:29:56 GMT

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.

Re: hourly breakdown of data

sanjujeeboy — Thu, 26 Nov 2020 10:41:21 GMT

@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

Re: hourly breakdown of data

edwin — Thu, 26 Nov 2020 13:11:31 GMT

try this corrected for 0 hr

Re: hourly breakdown of data

Kushal_Chawda — Thu, 26 Nov 2020 13:35:22 GMT

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

Re: hourly breakdown of data

sanjujeeboy — Fri, 27 Nov 2020 04:55:49 GMT

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

Re: hourly breakdown of data

Kushal_Chawda — Fri, 27 Nov 2020 09:55:13 GMT

@sanjujeeboy yes based on requirement if want them in order by you can use them. I don't think you need to add them in if condition but you can try