Sales Last hour. RangeSum and Peek Questions - Qlik Community

alvarogh27 · ‎2016-09-20

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

sunny_talwar · ‎2016-09-20

Not entirely sure how you came up with those numbers? Can you may be explain the calculation behind those two new columns you need?

alvarogh27 · ‎2016-09-21

I need two new columns. One of them with te Sum of Sales the last hour and the other with the count of clients last hour.

If the hour of de register is 07:30, the column of the sum sale must calculate the cumulative sum from 6:30 to 7:30, and the column of count client must count the number of register/rows. The first register in the second table (two new columns) are empty because there is not sales in the last hour.

Example for first register filled in new column Sum of Sales.

Client: 19 | Hour of sale: 7:34:56 | Amount sale: 34 | Sum sales in last hour: 753 | Number of clients last hour: 19

I must to find the register from 6:34:57. (Last hour is 6:34:57 - 7:34:56);

First register which satisfy the condition is:

Client: 1 | Hour of sale: 6:35:56 | Amount sale: 10

To get 753 is : Amount sale Client 1: 10 + Amount sale Client 2: 21 + Amount sale Client 3: 15 + ... + Amount Client 19: 34

Next register:

Client: 20 | Hour of sale: 7:37:31 | Amount sale: 30 | Sum sales in last hour: 773 | Number of clients last hour: 19

I must to find the register from 6:37:32

First register which satisfy the condition is:

Client: 2 | Hour of sale: 6:39:18 | Amount sale: 21

To get 773 is: Amount sale Client 2: 21 +...+Amount sale Client 20: 30

I hope to explain

Thanks very much.

Regards.

Álvaro

Gysbert_Wassenaar · ‎2016-09-21

You can try an AsOf table for this: The As-Of Table. Instead of YearDiff and MonthDiff you'd use for example HourDiff and MinuteDiff. The concept is the same, so it should be fairly straightforward.

To be honest it seems like overkill though. I'd simply create an hour field and sum and count per hour instead of selecting a time like 7:39:46 and calculating from there. I don't see the added benefit of doing that.

talk is cheap, supply exceeds demand

alvarogh27 · ‎2016-09-21

The idea is to find the hour with more clients and more sales in a line of cashier with one minute precision but the data have second precision. It's to manager rows in distinct center of sales.

I'll try it

Thank you for you response.

Anil_Babu_Samineni · ‎2016-09-21

How are you calculating last hour? I mean you want to show only sales when last 1 hour / 45 Min

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

sunny_talwar · ‎2016-09-21

One solution could be like this:

Table:

LOAD Checker,

Client,

Time#(Hour, 'h:mm:ss') as Hour,

Sales

FROM

[https://community.qlik.com/thread/233403]

(html, codepage is 1252, embedded labels, table is @1);

Temp:

LOAD Min(Hour) + 1/24 - 1/(24*60*60) as MinHour

Resident Table;

LET vMin = Peek('MinHour');

DROP Table Temp;

Table2:

LOAD Checker,

Client as New_Client,

If(Hour >= $(vMin), Hour) as Upper,

Time(If(Hour >= $(vMin), Hour - 1/24 + 1/(24*60*60))) as Lower,

Sales as New_Sales

Resident Table;

Left Join (Table)

IntervalMatch(Hour, Checker)

LOAD Lower,

Upper,

Checker

Resident Table2;

On Front end create a straight table

Dimension

New_Client

Expression

Sum(Sales)

Count(Client)

sunny_talwar · ‎2016-09-21

Or a script based solution

Table:

LOAD Checker,

Client,

Time#(Hour, 'h:mm:ss') as Hour,

Sales

FROM

[https://community.qlik.com/thread/233403]

(html, codepage is 1252, embedded labels, table is @1)

Where Len(Trim(Checker)) > 0;

Temp:

LOAD Min(Hour) + 1/24 - 1/(24*60*60) as MinHour

Resident Table;

LET vMin = Peek('MinHour');

DROP Table Temp;

Table2:

LOAD Checker,

Client as New_Client,

If(Hour >= $(vMin), Hour) as Upper,

Time(If(Hour >= $(vMin), Hour - 1/24 + 1/(24*60*60))) as Lower,

Sales as New_Sales

Resident Table;

Left Join (Table)

IntervalMatch(Hour, Checker)

LOAD Lower,

Upper,

Checker

Resident Table2;

Left Join (Table)

LOAD *

Resident Table2;

DROP Table Table2;

Left Join(Table)

LOAD Checker,

New_Client as Client,

Sum(Sales) as [Sum sales Last hour],

Count(Client) as [Count Client Last hour]

Resident Table

Group By Checker, New_Client;

alvarogh27 · ‎2016-09-22

Thank you very much.

It doesn't work.

sunny_talwar · ‎2016-09-22

That was a very unhelpful response my friend. What did not work? The image looks exactly like what you wanted, may be you can throw some light on how it did not work?

Best,

Sunny