topic Re: cumulative count over time duration in App Development

cumulative count over time duration

Arun_141 — Sat, 16 Nov 2024 18:24:42 GMT

Hi Experts,

I want to show the cumulative count of ID for the different status in bar chart. For example ID T-2 is Open on Jan-25-20 and moved to In Progress on Feb-29-20, so ID T-2 should show Open Status from Jan-25-20 till Feb-22-20( previous week of Feb-29-20), similarly for ID T-3 the Open date is Jan-18-20 and it moved to Hold on Mar-14-20, so ID T-2 should show Open Status from Jan-18-20 to Mar-07-20. So the count of IDs which are in Open State will be 1(18 -Jan-20), 2 (from Jan-25-20 to Feb-22-20) and 1 (Feb -29-20 to Mar-07-20). Similarly i want to show for Hold, Done and In Progress.

Below is the sample data which i am using.Week field has data available from Jan-18-20 to Apr-04-20.

Status	ID	Week
Open	T-2	Jan-25-20
In Progress	T-2	Feb-29-20
Done	T-2	Mar-28-20
Open	T-3	Jan-18-20
Hold	T-3	Mar-14-20

Output

Re: cumulative count over time duration

Kushal_Chawda — Sun, 05 Jul 2020 13:10:33 GMT

Please provide more sample data to work on

Re: cumulative count over time duration

Arun_141 — Sun, 05 Jul 2020 13:33:17 GMT

@Kushal_Chawda thanks for the reply.

Please find the attached excel for more sample data.

Re: cumulative count over time duration

Kushal_Chawda — Sun, 05 Jul 2020 14:49:11 GMT

Is your Week value stored as Date? What is the weekstart day?

Re: cumulative count over time duration

Arun_141 — Sun, 05 Jul 2020 15:14:29 GMT

@Kushal_Chawda

Yes week is stored as Date. I am using DATE(Week,'MMM-DD-YY') to convert in this format. weekstart day is Saturday.

Re: cumulative count over time duration

Kushal_Chawda — Sun, 05 Jul 2020 19:13:01 GMT

If I understood it correctly you ca do something like below

Data: LOAD ID, "Week" as Week1, Status FROM [Sample data.xlsx] (ooxml, embedded labels, table is Sheet1); New: NoConcatenate Load *, Week1 as FromWeek, if(Previous(ID)<>ID,Week1,Previous(Week1)) as ToWeek Resident Data Order by ID, Week1 desc; Drop Table Data; Final: Load *, WeekStart(FromWeek,IterNo()-1,5) as Week Resident New while WeekStart(FromWeek,IterNo()-1,5) <= WeekStart(ToWeek,-1,5); Drop Table New; Drop Field Week1;

Now you can create stack bar graph

Dimension:

Week

Status

Measure:

count(ID)

Re: cumulative count over time duration

Arun_141 — Mon, 06 Jul 2020 05:20:24 GMT

@Kushal_Chawda thanks for the reply.

I have used the solution which you have provided, but the count is not coming for the IDs which are in Hold and Done Status. Could you please check.

Sample Data

ID	Week	Status
T-2	Jan-25-20	Open
T-2	Feb-29-20	In Progress
T-2	Mar-28-20	Done
T-3	Jan-18-20	Open
T-3	Mar-14-20	Hold

Current Output

Desired Output

Re: cumulative count over time duration

Kushal_Chawda — Mon, 06 Jul 2020 09:24:11 GMT

Hi Kumar,

As per the sample data provided I can see the status for Hold and not for done because my understanding is that once it is done we don't need to look further. Can you also post output need for the sample data provided?

Re: cumulative count over time duration

Kushal_Chawda — Mon, 06 Jul 2020 10:39:36 GMT

try running this code. This should work

Data: LOAD ID, "Week" as Week1, Status FROM [lib://Data/Sample data.xlsx] (ooxml, embedded labels, table is Sheet1); New: NoConcatenate Load *, Week1 as FromWeek, if(Previous(ID)<>ID,Week1,weekstart(Previous(Week1),-1,5)) as ToWeek Resident Data Order by ID, Week1 desc; Drop Table Data; Final: Load *, WeekStart(FromWeek,IterNo()-1,5) as Week Resident New while WeekStart(FromWeek,IterNo()-1,5) <= WeekStart(ToWeek,0,5); Drop Table New; Drop Field Week1;

Re: cumulative count over time duration

Arun_141 — Mon, 06 Jul 2020 12:21:46 GMT

@Kushal_Chawda Thank you so much for your help.

I am getting correct result using above code.