Sales Last hour. RangeSum and Peek Questions

[alvarogh27]

Hi,

I need to calculate the Sum of Sales and the number of clients in the last hour of each checker.

For example:

I have this table:

Checker	Client	Hour	Sales
1	1	6:35:56	10
1	2	6:39:18	21
1	3	6:42:35	15
1	4	6:47:37	27
1	5	6:51:41	30
1	6	6:57:04	48
1	7	7:00:16	33
1	8	7:03:59	31
1	9	7:08:00	44
1	10	7:10:49	28
1	11	7:14:33	68
1	12	7:17:46	60
1	13	7:21:41	69
1	14	7:23:53	52
1	15	7:26:28	52
1	16	7:28:32	45
1	17	7:30:40	33
1	18	7:32:54	53
1	19	7:34:56	34
1	20	7:37:31	30
1	21	7:39:46	48
1	22	7:42:52	54
1	23	7:45:25	51
1	24	7:48:32	55

and I need to get:

Checker	Client	Hour	Sales	Sum sales Last hour	Count Client Last hour
1	1	6:35:56	10
1	2	6:39:18	21
1	3	6:42:35	15
1	4	6:47:37	27
1	5	6:51:41	30
1	6	6:57:04	48
1	7	7:00:16	33
1	8	7:03:59	31
1	9	7:08:00	44
1	10	7:10:49	28
1	11	7:14:33	68
1	12	7:17:46	60
1	13	7:21:41	69
1	14	7:23:53	52
1	15	7:26:28	52
1	16	7:28:32	45
1	17	7:30:40	33
1	18	7:32:54	53
1	19	7:34:56	34	753	19
1	20	7:37:31	30	773	19
1	21	7:39:46	48	800	19
1	22	7:42:52	54	839	19
1	23	7:45:25	51	890	20
1	24	7:48:32	55	918	20

I've tried to combinate Range Sum with Peek Function, but I don't know how to build a condition variable according to Hour Field and the last hour. For example, the range to get Sumsales for the last register (Client 24) is SumSales from client 5 (20 rows) while the range to get Sumsales for the register client 19 is Sum(Sales) from client 1 (19 rows).

Thank you.

Regards

Alvaro

[Anil_Babu_Samineni]

Haha Sunny, From your reply he might seen only Three 19's. But he is expecting four 19's?

1	19	7:34:56	34	753	19
1	20	7:37:31	30	773	19
1	21	7:39:46	48	800	19
1	22	7:42:52	54	839	19
1	23	7:45:25	51	890	20
1	24	7:48:32	55	918	20

[sunny_talwar]

Then I expect the OP mention that this is what is not working. A simple 'doesn't work' is not very helpful

[alvarogh27]

Hi Sunny,

Sorry for my short answer, it could seem that I didn't value your time and your great job. Sorry, I have no time and I tried one time and it doesn't work and I replied too quickly.

Today, I have tried again to run the application, and if I run your *.qvw in my computer, it works well, but if I copy and I paste in a new *.qvw, it doesn't work. I thought that it was for the format of Time.

In my qlikview app the format Time is:

SET TimeFormat='h:mm:ss';

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff]';

While in your *.qvw is:

SET TimeFormat='h:mm:ss TT';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

But, I changed this lines but it continues without to work.

I don't know why the Table2 is empty in a moment, and the columns upper and lower are not a time, is or empty or a number that is not a time.

When it creates the table2, Uppen and Lower I believe that Upper and Lower is not correct:

Finnally, I think that is a problem with de default formats, but is curious that doesn't work in my app-computer.

I'll try it to implement in my app, and I'll investigate why.

I was investigating to build a new column with the cumulative sum by checker, and after to make the difference between cumulative sum in a hour and the cumulative sum in a hour - 1 (with peek function), but the number inter-register is variable for the second cumulative sum and I don't know how make.

Again, Sorry for my comment and Thank you for your help. It's a great idea.

Regards, Alvaro.

[sunny_talwar]

I am guessing that you are going to be working on your end to make this work, right? Let us know how it goes and please share the final solution of how you got it to work (when it does)

Thanks,

Sunny