Get number of CloseDate_Day in a group of CloseDate_Month

Question

I want to get the sum of Amount of the company per month (CloseDate_Month) and divide this sum by the number of CloseDate_Day in this month.

q = load "Test";
q = group q by 'CloseDate_Month';
q = foreach q generate 'CloseDate_Month' as 'CloseDate_Month', sum('Amount') as 'sum_Amount';
q = order q by 'CloseDate_Month' asc;
q = limit q 2000;

I have this code generated by my Dashboard. Now I want to divide sum('Amount') by something like month_days('CloseDate_Day') but I have several problems here. First, the group by who only take 'CloseDate_Month' every other token are undefined in it. Finally, 'CloseDate_Day' is not a proper date for month_days() function.

Answer 1

To use the month_days() function you would need to convert to a date like the example below.

q = load "TEST_DATASET";
q = group q by ('CLOSE_DATE_Year', 'CLOSE_DATE_Month');
q = foreach q generate 'CLOSE_DATE_Year', 'CLOSE_DATE_Month',
month_days(toDate('CLOSE_DATE_Month'+"-"+'CLOSE_DATE_Year', "MM-yyyy")) as 'month_days',  
sum('AMOUNT') as 'AMOUNT',
sum('AMOUNT') / month_days(toDate('CLOSE_DATE_Month'+"-"+'CLOSE_DATE_Year', "MM-yyyy")) as 'AMT_per_day'
;