- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
hourly breakdown of data
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 |
- Tags:
- new_to_qlikview
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
try
=sum(Clicks)-rangesum(above(sum(Clicks)))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Edwin ,
Can you tell me how to achieve this in back end script?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
try this corrected for 0 hr
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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?
- « Previous Replies
-
- 1
- 2
- Next Replies »