topic Re: count() ongoing in App Development

count() ongoing

Dann — Thu, 27 May 2021 13:30:00 GMT

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

Re: count() ongoing

canerkan — Thu, 27 May 2021 15:04:57 GMT

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

Re: count() ongoing

Dann — Fri, 28 May 2021 20:31:45 GMT

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

Re: count() ongoing

canerkan — Mon, 31 May 2021 06:40:11 GMT

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

Re: count() ongoing

Dann — Tue, 01 Jun 2021 07:23:30 GMT

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

= (All the Pol created before Week x)-(All the POl closed before week x)

Thanks

Re: count() ongoing

canerkan — Tue, 01 Jun 2021 12:05:43 GMT

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

Re: count() ongoing

Dann — Wed, 02 Jun 2021 07:12:26 GMT

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

Re: count() ongoing

canerkan — Wed, 02 Jun 2021 06:33:41 GMT

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