Please help verify my coding

apichana — Thu, 28 Jan 2021 03:45:07 GMT

Hi,

I would like to ask for some help to verify my coding on Qlik Sense. My requirement is I want to count Plate_No who never fill GASOHOL E20 or GASOHOL E85 at all in each month. Below is my coding and sample of data. I add 3 calculate fields 'Plate_Month' , 'E20E85_Consumption' and 'Action_Need'

If user fills E20 or E85 , 1 will be show on E20E85_Consumption, If not 0 will be there.

And if user didn't fill E20 or E85 at all for each month " Action Need" will show on Action_Need field otherwise 'OK'. Action_Need filed come from

If(Sum(E20E85Consumption)/Count(PLATE_Month) = '0','Action Need','Ok') as Action_Need

Resident Fleet_Card
Group by PLATE_Month;

So I'm not sure my logic and coding are correct or not please help verify. Thank you.

Fleet_Card:

LOAD
Date([POST_DATE]) AS [POST_DATE-PostDate],
Month([POST_DATE]) AS [POST_MONTH-PostDate],
TRN_DATE as Date,
TRN_TIME,
COMPANY_NAME,
CUSTOMER_ID,
CREDIT_LINE,
DRIVER_NAME,
CARD_NO,
PLATE_NO,
PLATE_NO&'_'&TRN_DATE as PLATE_DATE,
PLATE_NO&'_'& Month(TRN_DATE) as PLATE_Month,

if(day([POST_DATE])<=25,month([POST_DATE]),month(AddMonths([POST_DATE],1))) AS [PostDate_MonthNew],
day([POST_DATE]) AS [AAA],

if([PRODUCT_NAME] ='GASOHOL E20'or 'GASOHOL E85',1,0) as E20E85Consumption,

RowNo() as RowCount

FROM [lib://FM/Fleet_Card_*.qvd]
(qvd)

Plate_RowCount:
Load
PLATE_Month,
Count (RowCount) as PlateRowCount,
If(Sum(E20E85Consumption)/Count(PLATE_Month) = '0','Action Need','Ok') as Action_Need
Resident Fleet_Card
Group by PLATE_Month;

Post_Date	Plate_No	Plate_Month	User	Product	E20E85_Consumption	Action_Need
01-Jan-21	1234	1234_Jan	AA	DIESEL B7	0	Action Need
10-Jan-21	1234	1234_Jan	AA	DIESEL B7	0
01-Jan-21	2222	1234_Jan	BB	GASOHOL E20	1	Ok
12-Jan-21	2222	2222_Jan	BB	GASOHOL E285	1
13-Jan-21	2222	2222_Jan	BB	GASOHOL91	0
02-Jan-21	3333	3333_Jan	CC	GASOHOL E20	1	Ok
08-Jan-21	3333	3333_Jan	CC	GASOHOL E20	1
01-Feb-21	1234	1234_Feb	AA	DIESEL B7	0	Ok
05-Feb-21	1234	1234_Feb	AA	GASOHOL E20	1
12-Feb-21	2222	2222_Feb	BB	GASOHOL E285	1	Ok
13-Feb-21	2222	2222_Feb	BB	GASOHOL E20	1
02-Feb-21	3333	3333_Feb	CC	DIESEL B7	0	Action Need
08-Feb-21	3333	33333_Feb	CC	DIESEL B7	0

topic Please help verify my coding in QlikView

Please help verify my coding