count() ongoing

[Dann]

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

[canerkan]

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

[Dann]

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

[canerkan · Accepted solution]

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

[Dann]

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

[canerkan]

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

[Dann · Accepted solution]

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

[canerkan]

You are welcome. And if you were able to solve this problem on your own even better! 😁