Count

[qlikview979]

Hi Experts,

please find my attached files.

I want the count between 9 am to 20pm.

Regards,

Mahesh.

Output:-

Super Plus	Super	Regular	Spike	Casual	Incoming	Zero	Total
937	1352	2866	3	640	716	194	6708

[sunny_talwar]

This?

Script:

Table:

LOAD Time(Frac([UPC Generate Date & Time])) as Time,

  [UPC Generate Date & Time],

     Category

FROM

time.xlsx

(ooxml, embedded labels, table is Sheet1);

Expression:

=Count({<Time = {"$(='>=' & Time(MakeTime(9)) & '<=' & Time(MakeTime(20)))"}>}Category)

[sasikanth · Accepted solution]

try this exp:

=count(DISTINCT{<Hour={">=9 <=20"}>} Key_Value)

temp:

load *, RowNo() as Key_Value;

LOAD [UPC Generate Date & Time],

hour( [UPC Generate Date & Time]) as Hour,

Category

FROM

[..\Downloads_Web\time.xlsx]

(ooxml, embedded labels, table is Sheet1);

[PrashantSangle]

Hi,

try like

LOAD Hour([UPC Generate Date & Time]) as hr,

     Category

FROM

time.xlsx

(ooxml, embedded labels, table is Sheet1);

In straight table

dimension:  Category

expression : count({<hr={">=9<=20"}>}Category)

Regards

[swuehl]

Or maybe like this, creating different output:

Table:

LOAD *, Hour([UPC Generate Date & Time]) as Hour;

LOAD Time(Frac([UPC Generate Date & Time])) as Time,

  [UPC Generate Date & Time],

     Category

FROM

time.xlsx

(ooxml, embedded labels, table is Sheet1);

=Count({<Hour = {">=9<=20"}>}Category)

Category	Count
	6729
Casual	644
Incoming	717
Regular	2879
Spike	3
Super	1353
Super Plus	939
Zero	194

=Count({<Hour = {">=9<20"}>}Category)

Category	Count
	6075
Casual	578
Incoming	635
Regular	2593
Spike	3
Super	1226
Super Plus	856
Zero	184

[qlikview979]

Hi Sunny,

I Given my Expected output above.

and when i am loading this field "[UPC Generate Date & Time]" i am getting time   12:00:00 am to 11:59:59 pm .

[qlikview979]

Hi Experts,

I got Manually working file.

Please find this

[sunny_talwar]

I added a new field in the script using the time function:

Time(Frac([UPC Generate Date & Time])) as Time,

Not sure if I can get the number you are looking for. I tried various combination of time's but it isn't working. Can you check if the numbers are right above?

[sunny_talwar]

There are 6729 numbers of rows

Try this: =Count({<Time = {"$(='>=' & Time(MakeTime(9)) & '<' & Time(MakeTime(21)))"}>}Category)

[qlikview979]

Hi Experts,

I got the output earlier  from  Sasi K.please consider this   I given  correct answer to  Sasi k,  I tried with remaining people script also

It's  working Good. Tanks for your  Support.

Regards,

Mahesh.