cumulative count over time duration

[Arun_141]

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

[Kushal_Chawda]

Please provide more sample data to work on

[Arun_141]

@Kushal_Chawda  thanks for the reply.

Please find the attached excel for more sample data.

[Kushal_Chawda]

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

[Arun_141]

@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.

[Kushal_Chawda]

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)

[Arun_141]

@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

[Kushal_Chawda]

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?

[Kushal_Chawda · Accepted solution]

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;

[Arun_141]

@Kushal_Chawda  Thank you so much for your help.

I am getting correct result using above code.