Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Calculate total Customer Count from accumulate in and out counts at fixed time interval

Hi, Community,

i have the followng sample data...

i would like to calculate the hourly , daily and monthly total customer count  with the below data,

i have create a cyclic timegroup with month, day and hour dimension, but i dont know how to write the expression,...

Siteraw_tl_history_datetimetl_history_datetimeforcustomercounttl_history_counter_intl_history_counter_out
A2016-09-05 09:305/9/201600
A2016-09-05 10:005/9/201653
A2016-09-05 10:305/9/201665
A2016-09-05 11:005/9/20161414
A2016-09-05 11:305/9/20162423
A2016-09-05 12:005/9/20163425
A2016-09-05 12:305/9/20164436
A2016-09-05 13:005/9/20164640
A2016-09-05 13:305/9/20164846
A2016-09-05 14:005/9/20164948
A2016-09-05 14:305/9/20165349
A2016-09-05 15:005/9/20165554
A2016-09-05 15:305/9/20165856
A2016-09-05 16:005/9/20166257
A2016-09-05 16:305/9/20166559
A2016-09-05 17:005/9/20166760
A2016-09-05 17:305/9/20167060
A2016-09-05 18:005/9/20167661
A2016-09-05 18:305/9/20168062
A2016-09-05 19:005/9/20168863
A2016-09-05 19:305/9/20169064
A2016-09-05 20:005/9/201611167
A2016-09-05 20:305/9/2016121100
A2016-09-05 21:005/9/2016131111
A2016-09-05 21:305/9/2016144144
A2016-09-06 09:306/9/201600
A2016-09-06 10:006/9/201663
A2016-09-06 10:306/9/201685
A2016-09-06 11:006/9/20161614
A2016-09-06 11:306/9/20162018
A2016-09-06 12:006/9/20163532
A2016-09-06 12:306/9/20164544
A2016-09-06 13:006/9/20165046
A2016-09-06 13:306/9/20165147
A2016-09-06 14:006/9/20165350
A2016-09-06 14:306/9/20165751
A2016-09-06 15:006/9/20165952
A2016-09-06 15:306/9/20166053
A2016-09-06 16:006/9/20167867
A2016-09-06 16:306/9/20168068
A2016-09-06 17:006/9/20168870
A2016-09-06 17:306/9/20169080
A2016-09-06 18:006/9/20169288
A2016-09-06 18:306/9/20169489
A2016-09-06 19:006/9/20169991
A2016-09-06 19:306/9/201610493
A2016-09-06 20:006/9/201611199
A2016-09-06 20:306/9/2016131120
A2016-09-06 21:006/9/2016140133
A2016-09-06 21:306/9/2016145145

for example, at the 10 hour data point, it should show 19( last record of the hour( indicate by the first record of the next hour)-first record of the hour)..

calculate from :

14-5=9 +

16-6=10

at the 5 day data point, it should show 144..

144-0 =144( last record of the day - first record of the day)

is that possible...

3 Replies
Highlighted

Re: Calculate total Customer Count from accumulate in and out counts at fixed time interval

So, based on the dimension you are on, you would want to see the This Period - Last Period?

Highlighted
Not applicable

Re: Calculate total Customer Count from accumulate in and out counts at fixed time interval

thanks for replying sunny.

sunny , i am thinking to use last period -this period, because for example, when i change to hour dimension of the cyclic group, it shows the total number of customer walked in the store at that hour period, but the record is taken in 30 min interval...so.i think..e.g at the 10 hour data point, use the last period of the record(since no record is taken at 10:59, so use the first record of the next hour, that is  the record taken at 11:00)-first record of the hour(the one record taken at 10:00)..

the "in" column is the accumulate customer walked in, so when i find the difference between this 2 time , i can know how many customers have been walked in the site at the 10th hour period, that is from 10:00 to 10:59...

i have tried for a while but cannot fix it...

Highlighted
Not applicable

Re: Calculate total Customer Count from accumulate in and out counts at fixed time interval

i guess it also make sense  to use max(this period) -min(this period) and us a line chart to present it, like attachement testcount3.qvw...Thanks~

222.JPG