Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
alvarogh27
Contributor II
Contributor II

Sales Last hour. RangeSum and Peek Questions

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:

 

CheckerClientHourSales
116:35:5610
126:39:1821
136:42:3515
146:47:3727
156:51:4130
166:57:0448
177:00:1633
187:03:5931
197:08:0044
1107:10:4928
1117:14:3368
1127:17:4660
1137:21:4169
1147:23:5352
1157:26:2852
1167:28:3245
1177:30:4033
1187:32:5453
1197:34:5634
1207:37:3130
1217:39:4648
1227:42:5254
1237:45:2551
1247:48:32

55

and I need to get:

   

CheckerClientHourSalesSum sales Last hourCount Client Last hour
116:35:5610
126:39:1821
136:42:3515
146:47:3727
156:51:4130
166:57:0448
177:00:1633
187:03:5931
197:08:0044
1107:10:4928
1117:14:3368
1127:17:4660
1137:21:4169
1147:23:5352
1157:26:2852
1167:28:3245
1177:30:4033
1187:32:5453
1197:34:563475319
1207:37:313077319
1217:39:464880019
1227:42:525483919
1237:45:255189020
1247:48:325591820

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

13 Replies
sunny_talwar

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
Contributor II
Contributor II
Author

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

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
Contributor II
Contributor II
Author

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

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

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)

Capture.PNG

sunny_talwar

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;


Capture.PNG

alvarogh27
Contributor II
Contributor II
Author

Thank you very much.

It doesn't work.

sunny_talwar

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