Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
i have a list of Pol with ID, Creation_date and Closure_date,
I want to represente the number of Pol ongoing for each week (from 2019 to date) in a line chart(dimension=week(Date), measure=count(ID) ongoing)
i already : Load ID, Creation_date as Date
'creation' as FlagDate resident Data;
Load ID, Closure_date as Date
'closure' as FlagDate resident Data;
i'm looking for how to get the ongoing Pol for eack week
A set expression to have something like this :
For each D in Date
ongoing_D=0
For each C in POL ID
If Creation_date[C] <D<Closure_date[C] or Creation_date[C] <D and Closure_date[C] = ' ' Then
ongoing_D=ongoing_D+1
Thanks
Dann
Hi Dann,
sorry for my late reply. You have loaded your TABLE beforehand so you need to use "Resident TABLE" in the loop. I also recognised that i forgot to name the new field in the loop.. sorry for that. Try again with this script and let me know if it worked:
Set vListYears = '2019','2020', '2021';
PolPerWeekYear:
Load * Inline [
WeekYear, CountPol
];
For each vYear in $(vListYears)
For vWeek = 1 to 52
Concatenate (PolPerWeekYear)
Load
Week(Creation_date) & '-' & Year(Creation_date) as WeekYear,
Count(ID) as CountPol
Resident TABLE
where Week(Creation_date) & '-' & Year(Creation_date) = $(vWeek) & '-' & $(vYear)
Group by Week(Creation_date) & '-' & Year(Creation_date);
Next
Next
Regards,
Can
Hi can,
Thanks for helping, i tried it but the table loaded is empty.
I noticed that for a week n:
Ongoing(n)=Ongoing(n-1)+Created(n)-Closed(n)==> Ongoing(n) = (Sum(Created(k)-Closed(k)), k from 1 to n).
So i wrote this code and it works.
//at load editor
Load ID, Creation_date as Date,
'creation' as Sign resident Data;
Load ID, Closure_date as Date;
'closure' as Sign resident Data;
//dimension: week(Date)
//Measure=
RangeSum(Above( ( count({<Sign={'creation'}>}distinct ID) ) , 0, RowNo()))-RangeSum(Above( ( count({<Sign={'closure'}>}distinct ID) ) , 0, RowNo()))
Regards,
Dann
Hi Dann,
you could try this in your load script to buil:
Set vListYears = '2019','2020', '2021';
PolPerWeekYear:
Load * Inline [
WeekYear, CountPol
];
For each vYear in $(vListYears)
For vWeek = 1 to 52
Concatenate (PolPerWeekYear)
Load
Week(Creation_date) & '-' & Year(Creation_date),
Count(ID)
From TABLE
where Week(Creation_date) & '-' & Year(Creation_date) = $(vWeek) & '-' & $(vYear)
Group by Week(Creation_date) & '-' & Year(Creation_date);
Next
Nextxt
and then use "WeekYear" as Dimension and Sum(CountPol) as measure on your line chart.
I didn't test this script but you should get an idea of where i am trying to go there.
Hope this helps,
Can
Hi Can,
Thank you for your answer
i have tried this
but i always have an error
ie: Ongoing Pol at week x = Pol created before and during week x and not closed before or during week x
Hi Dann,
sorry for my late reply. You have loaded your TABLE beforehand so you need to use "Resident TABLE" in the loop. I also recognised that i forgot to name the new field in the loop.. sorry for that. Try again with this script and let me know if it worked:
Set vListYears = '2019','2020', '2021';
PolPerWeekYear:
Load * Inline [
WeekYear, CountPol
];
For each vYear in $(vListYears)
For vWeek = 1 to 52
Concatenate (PolPerWeekYear)
Load
Week(Creation_date) & '-' & Year(Creation_date) as WeekYear,
Count(ID) as CountPol
Resident TABLE
where Week(Creation_date) & '-' & Year(Creation_date) = $(vWeek) & '-' & $(vYear)
Group by Week(Creation_date) & '-' & Year(Creation_date);
Next
Next
Regards,
Can
Hello Canerkan,
Thank you!
This code works and it gives the number of POL created for each week.
i'm know trying to have the ongoing POL by week:
Ongoing Pol at week x = All the Pol created before and during week x and not closed before or during week x
or
= (All the Pol created before Week x)-(All the POl closed before week x)
Thanks
You could replace the Where-Clause with this:
where (Week(Creation_date) <= $(vWeek) and Year(Creation_date) <= $(vYear)) and ((Year(Closure_date) >= $(vYear) and Week(Closure_Date) > $(vWeek)) or Closure_date = 'N/A')
Let me know if it worked.
Regards,
Can
Hi can,
Thanks for helping, i tried it but the table loaded is empty.
I noticed that for a week n:
Ongoing(n)=Ongoing(n-1)+Created(n)-Closed(n)==> Ongoing(n) = (Sum(Created(k)-Closed(k)), k from 1 to n).
So i wrote this code and it works.
//at load editor
Load ID, Creation_date as Date,
'creation' as Sign resident Data;
Load ID, Closure_date as Date;
'closure' as Sign resident Data;
//dimension: week(Date)
//Measure=
RangeSum(Above( ( count({<Sign={'creation'}>}distinct ID) ) , 0, RowNo()))-RangeSum(Above( ( count({<Sign={'closure'}>}distinct ID) ) , 0, RowNo()))
Regards,
Dann
You are welcome. And if you were able to solve this problem on your own even better! 😁