Qlikview count all the product in the last 12hours

Question

Given that I have the following data:

I want to count the number of distinct product sold in the passed 8 hours, Suppose the time now is 26/10/2018 23:35:00.

The expected result should be as follows:

I am working in qlikview. I do not know how to do the expression to get what i want especially when it concern datetime. should i use aggreegate or count?

Thank you in advance for answering!

Kindly follow below link for this solution. [how-to-group-time-by-hour-or-by-10-minutes](https://stackoverflow.com/questions/5002661/how-to-group-time-by-hour-or-by-10-minutes) — Saket Yadav, Oct 26 '18 at 05:03

score 0 · Answer 1 · answered Oct 26 '18 at 06:48

0

Kindly try this for required output 
    DECLARE @CURR_DATE DATETIME
SET @CURR_DATE=DATEADD(hour,0,'10/26/2018 23:30')

SELECT Product, count(category)
  FROM  Table_1 
where 
  AddedDate between  DATEADD(hour,-1,@CURR_DATE) and @CURR_DATE  
  GROUP BY Product,category,(DATEPART(hh, AddedDate))

answered Oct 26 '18 at 06:48

Saket Yadav

967
1
10
23

This is an SQL syntax, not a Qlikview one. – BrunoMarques Oct 26 '18 at 09:03

score 0 · Answer 2 · answered Oct 26 '18 at 09:02

I would recomend to define a flag for each record in the script to avoid tricky and complicated set analysis that could easily break.

Assumming you LOAD the Datetime somewhere, I would add :

LOAD
...
Datetime,
If(Datetime > Today() - 8 / 24, 'Y', 'N') as SoldInLast8Hours
....

Having this new dimension will allow you to use a simple set analysis like :

Sum({<SoldInLast8Hours = {'Y'}>} 1)

Or

Count({<SoldInLast8Hours = {'Y'}>} DISTINCT EntryId)

If you have an unique id for each transaction row.

Qlikview count all the product in the last 12hours

2 Answers2