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;
