topic Re: Cohort for two Formulas in QlikView

Cohort for two Formulas

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I need to do a Cohort Analysis likewise the one which was done by sunindia‌ attached at the bottom. This time I need to show the New Revenue and Churn. I need to show the Cust Internal ID which added new revenue and the IDs which churned. In order for one to find the new revenue and churn in the Excel sheet attached just remove the - hyphen from the filters of the columns and one will view the data.

Please provide insights.

Thanks,

Re: Cohort for two Formulas

sunny_talwar — Wed, 06 Apr 2016 22:12:31 GMT

Do you have a plan on how it is going to look?

Re: Cohort for two Formulas

Wed, 06 Apr 2016 22:17:53 GMT

Hi Sunny, thanks for the responding. This time the good part is we've been provided a months field so no need for a cross table. Something that looks like the below table. Lets start from there if it's possible please.

Re: Cohort for two Formulas

sunny_talwar — Wed, 06 Apr 2016 22:27:26 GMT

Month field is provided? Not sure what you mean? You don't need a pivot style table this time?

Re: Cohort for two Formulas

Wed, 06 Apr 2016 22:29:50 GMT

In the Qlikview file attached you'll find the details. Please like the table below but this time no change in existing column.

Thanks,

Re: Cohort for two Formulas

sunny_talwar — Wed, 06 Apr 2016 22:31:40 GMT

Where would that number go then (I mean 'Change in Existing')?

Re: Cohort for two Formulas

Thu, 07 Apr 2016 12:47:16 GMT

Thanks Sunny, I want something like the below. You can neglect change in existing column because it doesn't concern us in this report.

Re: Cohort for two Formulas

sunny_talwar — Thu, 07 Apr 2016 13:12:25 GMT

It would be easier if you can fill in the values you are expecting to see in each of those cells

Re: Cohort for two Formulas

Thu, 07 Apr 2016 14:18:07 GMT

Thanks Sunny for instructing me on that, it actually made me realize my mistakes.

This is the depiction of the table in Qlik.

Please provide assistance.

Re: Cohort for two Formulas

sunny_talwar — Thu, 07 Apr 2016 15:02:30 GMT

Not sure how you are calculating the two Quantity fields, but the Dollar amount seems pretty straight forward:

Re: Cohort for two Formulas

Thu, 07 Apr 2016 15:19:59 GMT

Thanks Sunny, how can I make the above chart from the table? This will determine the validity of the Quantity fields.

Please provide assistance!!!

Re: Cohort for two Formulas

sunny_talwar — Thu, 07 Apr 2016 15:22:00 GMT

Not sure I understand. The chart was already created, right. Why are we recreating it?

Re: Cohort for two Formulas

Thu, 07 Apr 2016 15:26:36 GMT

The above chart was part of one set of data. The chart I need to make now is part of another set of data. The above chart was part of the STK Excel sheet while this new chart (with your kind assistance) will be of Book1 Excel sheet.

Re: Cohort for two Formulas

Thu, 07 Apr 2016 17:09:12 GMT

Hi Sunny,

Do you've any insights on this matter?

Re: Cohort for two Formulas

sunny_talwar — Thu, 07 Apr 2016 17:11:17 GMT

I am looking, I don't have anything as of now. I need Kush141087‌ here .

Re: Cohort for two Formulas

Thu, 07 Apr 2016 17:12:54 GMT

Thanks for the reply!!!!!!

Re: Cohort for two Formulas

sunny_talwar — Thu, 07 Apr 2016 17:47:25 GMT

How is this looking?

Re: Cohort for two Formulas

Thu, 07 Apr 2016 22:10:10 GMT

Hi Sunny,

I'm attaching the Book1 sheet within I've done pivot tables for both Churn and New Revenue. The benefit of the pivot table....I was able to get the unique IDs for the New Revenue and Churn. Please provide any assistance!!!! The quantity doesn't seem to match up.

Re: Cohort for two Formulas

sunny_talwar — Thu, 07 Apr 2016 22:34:10 GMT

You earlier provided me with this:

The number for dollar are matching with this, are we only talking about Quantity still or we have incorrect numbers for Dollar as well?

Re: Cohort for two Formulas

Fri, 08 Apr 2016 13:04:22 GMT

The dollar figures look good, that's not the concern; the concern is the quantity. I'm attaching sheet where I've highlighted how the quantity should look like or be close to.

New Revenue

Churn