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.


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'
| improve this answer | |
  • It doesn't really work like I want, I want the number of closure day not the day of the month. I just realized that. So I need to count the number of CloseDate_Day in the Month – Jack Apr 3 '19 at 20:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.