Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Can anyone explain , how can we add where condition for Wo_completionDate between the beginning of prior month and the end of current month(two months data like this month and previous month) and i need to put each KPI in a for loop to calculate PKI for each month.
Please check commented lines in code i.e where we need to implement the code .Please find the attached documents.
Code:
Summary:
LOAD wo_number,
pri_code,
wc_code,
Proj_number,
Client_source,
wo_completionDate
FROM
(ooxml, embedded labels, table is Sheet1);
let vMinMonth = min(num(month(floor(wo_completionDate)),'00'));
let vMaxMonth = max(num(month(floor(wo_completionDate)),'00'));
let vMinYear=min(year(floor(wo_completionDate)));
let vMaxYear=max(year(floor(wo_completionDate)));
//loop should start for each month KPI1 calculation
left join(Summary)
LOAD
Client_source,
count(wo_number) as KPI1
resident Summary where pri_code= '1'
//where condition has to be here for Wo_completionDate between the beginning of prior month and the end of current month
group by Client_source;
//loop should start for each month KPI2 calculation
left join(Summary)
LOAD
Client_source,
count(wo_number) as KPI1
resident Summary where pri_code= '2'
//where condition has to be here for Wo_completionDate between the beginning of prior month and the end of current month
group by Client_source;
Regards,
Ganesh
The condition would be
Where Wo_completionDate >= MonthStart(Today(), -1) And Wo_completionDate <= MonthEnd(Today())
Your second question is about the code. You cannot do a join inside a loop. This is because a join adds fields to the table. After the first iteration of the loop, the fields have already been added, so they will be considered part of the join key, rather than fields to add.
The solution is to build each table inside the loop, and then join the tables after the loop completes.
Ganesh,
I'm not going to attempt to answer your question here as I've posted some suggestions on a very similar thread you started a couple of days ago, and I don't think you understood what I suggested that time.
I will however point out what I think might be an error in your code.
Your Summary table includes wo_number (work order number?). Surely this is transaction level data, and not the level at which you want to summarise? Especially as your KPI 1 calculation is 'count(wo_number)'.
Marcus
Thanks for your suggestion jonathan, now i understood how to add looped data to summary table here. But in my first question about where clause is to get data for every month in data, not only for this month and previous month data. Your suggestion for where clause will only give september 2015 and october 2015 data. Actually this is where i got stuck in my previous thread as well.
Thank for you suggestion Marcus, I took your suggestions in my implementation, but i asked one more query on that thread which is similar to this thread, it may be a overlook. If i bothered you with my actions, sorry for that.
Best Regards,
Ganesh