Cummulative count

Question

Hi,

Is there any way in which I can find the week over week cummulative distinct count of the IDs using any of the Alteryx tools. I have attached the scenario below:-
I can do this in SQL by using self join but is there any way of achieving this is Alteryx. Please let me know.
Thanks

CharlieS · Accepted Answer

@rprashar

Solution is attached. There's probably a more simple way to accomplish this, but this works.

rprashar · Accepted Answer

Thanks for the solution it worked for me.

Is there any way in which we can do this by conditional self join technique in Alteryx. In SQL we can just write a conditional self join on ID and this problem gets solved. In Alteryx we have append tool which does the cartesian product, but is there any way we can do conditional cross/self join ?

rprashar · Accepted Answer

Sorry John using an older version of Alteryx, was not able to see this.

rprashar · Accepted Answer

Hi @CharlieS ,

Thanks for the help.

But consider the case below:-

The above example is a special case in which some IDs are not there in some weeks. For example geo - a doesn't have wk2, but we are showing it in the output without changing the value of the running count.
So I want these missing weeks in the output, which is not getting solved in this solution, if you can help me with this special case.

Thanks

CharlieS · Accepted Answer

@rprashar

The first thing to do is construct a list of all potential outputs. Then merge this with the other results and fill in gaps where necessary. Solution attached.

rprashar · Accepted Answer

Hi @CharlieS ,

Thanks for the solution.

However, I am getting an error that there are more than 16 records in the source during appending.

I have 104 weeks and approx 70 geos. I need a Cartesian product of both of them to get all the combinations.

Can you please help me with this error.

Thanks

JohnJPS · Accepted Answer

Hi @rprashar,

Just for completeness, I've modified the solution I provided earlier to account for missing weeks; (attached, and you'll have to tweak the version as described earlier to run on your version).

John