This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have a large dataset. That is with rows when transposed, have 26,874,045. A sample data is attached.
When we include Append option, the same is taking a lot of time. The workflow didn't even complete after 15 Hours.
The data have:
2. Selling Points
3. Date: from 01-10-2018 to 01-12-2020
4. Values: 1 - They ordered. 0- They Didn't ordered
Condition to take into Consideration:
To Calculate Lost Customer Rate per Month: Count of Lost Flag in that Particular Month / Count of Active Sell Points in the Previous Month
1. Group by Brand then Group by Selling Points.
2. After 1, if 3 or more consecutive zeros come it is considered as 1 Lost flag. The month at which the pattern of consecutive 3 or more zero started is the first Lost Month and only that month is taken for Lost calculation. So in the below example:
The zeros should be Calculated for each brand under each sell point separately
For example: For sell point 20 under Brand A1, it should first check for the first one, then look for the zeros. For sell point 21 under Brand A1, the same way it should look for first one and then zeros. So as Brand and Sell Point Changes, it should again look for the first one the start counting zeros.
01/06/2020 is one Lost Flag and 01/02/2019 is another Lost Flag for the Same Brand (A2) in sell point (8). So in this example only 2 Lost Flag. We are not taking 01/07/2020, 01/07/2020, for Lost Calculation but they are still inactive Sell Point.
3. If there are less than 3 consecutive zeros, it should be considered as sales active months.
For Example: Here the two zero months should be considered as active month for Sell Point:
4. Calculate Count of Active Sell Points in the Previous Month: If we are calculating for April, we should get the total count of One in March (Please refer the first screenshot.
Once this flags are achieved we can ignore the group by Sell Point and bring in Group By Date, so that we can calculate it for particular month.
Sample data is attached.
Is it possible to create a workflow without using the Append Function?
Perhaps @atcodedog05 can modify his workflow as it seems to be almost there!
But I can explain the last step. With the multi-row formula tool, you can set the "Values for Rows that don't Exist" to Null. By doing this, you can add an additional clause to the IF statement to say "IF the previous row value is null, then set as 1" as an example.
With the above said, that addition should have all the logic you need with these groups.
One more condition mentioned was only first month of the 3 or more consecutive months should be taken for the Inactive calculation. Really sorry if I confused you. So in the below example, only for 01/06/2020 - It is inactive. Rest of the zeros shouldn't be taken for Inactive calculation.