Tableau using window_max at the customer level (Cohort analysis)
I am having a calculation problem on Tableau Desktop. I have attached a data set and tableau sheet as pictures to illustrate the problem. I have a list of order_id, customer_id, order_date, and then a column with the order number for each customer_id.
I want to calculate the evolution of the number of orders made for each of my cohorts.
Cohort : {FIXED [Customer Id] : MIN([Order Date])}
I then calculate :
AVG({INCLUDE [Customer Id]: MAX([Count orders])})
However, if a specific customer did not make a purchase during a specific month, he is not included in the LOD calculation.
For instance, using my example,
The first cohort is composed of customer #1 and #2. Looking at the tableau workbook attached, in february the value calculated in blue is 3 (maximum value of customer #1 in february). However, I would like tableau to also take 2 (maximum value of customer #2 in january) and then make an average (2+3)/2=2.5. Because customer #2, did not make an order in february, he is not included in the calculation.
I was hoping to calculate this in the following way :
AVG (window_max ({ INCLUDE customer_id : MAX ( Count_orders )}, FIRST(), 0))
But it does not work because window_max has to take aggregate values.
Is there a way to overcome this ?
Thank you for your time and help.
data
tableau worksheet
tableau
edited Nov 13 '18 at 17:33
Siva
7,64282847
asked Nov 13 '18 at 16:45
Mathieu TardyMathieu Tardy
11
add a comment |
I am having a calculation problem on Tableau Desktop. I have attached a data set and tableau sheet as pictures to illustrate the problem. I have a list of order_id, customer_id, order_date, and then a column with the order number for each customer_id.
I want to calculate the evolution of the number of orders made for each of my cohorts.
Cohort : {FIXED [Customer Id] : MIN([Order Date])}
I then calculate :
AVG({INCLUDE [Customer Id]: MAX([Count orders])})
However, if a specific customer did not make a purchase during a specific month, he is not included in the LOD calculation.
For instance, using my example,
The first cohort is composed of customer #1 and #2. Looking at the tableau workbook attached, in february the value calculated in blue is 3 (maximum value of customer #1 in february). However, I would like tableau to also take 2 (maximum value of customer #2 in january) and then make an average (2+3)/2=2.5. Because customer #2, did not make an order in february, he is not included in the calculation.
I was hoping to calculate this in the following way :
AVG (window_max ({ INCLUDE customer_id : MAX ( Count_orders )}, FIRST(), 0))
But it does not work because window_max has to take aggregate values.
Is there a way to overcome this ?
Thank you for your time and help.
data
tableau worksheet
tableau
edited Nov 13 '18 at 17:33
Siva
7,64282847
asked Nov 13 '18 at 16:45
Mathieu TardyMathieu Tardy
11
add a comment |
I am having a calculation problem on Tableau Desktop. I have attached a data set and tableau sheet as pictures to illustrate the problem. I have a list of order_id, customer_id, order_date, and then a column with the order number for each customer_id.
I want to calculate the evolution of the number of orders made for each of my cohorts.
Cohort : {FIXED [Customer Id] : MIN([Order Date])}
I then calculate :
AVG({INCLUDE [Customer Id]: MAX([Count orders])})
However, if a specific customer did not make a purchase during a specific month, he is not included in the LOD calculation.
For instance, using my example,
The first cohort is composed of customer #1 and #2. Looking at the tableau workbook attached, in february the value calculated in blue is 3 (maximum value of customer #1 in february). However, I would like tableau to also take 2 (maximum value of customer #2 in january) and then make an average (2+3)/2=2.5. Because customer #2, did not make an order in february, he is not included in the calculation.
I was hoping to calculate this in the following way :
AVG (window_max ({ INCLUDE customer_id : MAX ( Count_orders )}, FIRST(), 0))
But it does not work because window_max has to take aggregate values.
Is there a way to overcome this ?
Thank you for your time and help.
data
tableau worksheet
tableau
edited Nov 13 '18 at 17:33
Siva
7,64282847
asked Nov 13 '18 at 16:45
Mathieu TardyMathieu Tardy
11
I am having a calculation problem on Tableau Desktop. I have attached a data set and tableau sheet as pictures to illustrate the problem. I have a list of order_id, customer_id, order_date, and then a column with the order number for each customer_id.
I want to calculate the evolution of the number of orders made for each of my cohorts.
Cohort : {FIXED [Customer Id] : MIN([Order Date])}
I then calculate :
AVG({INCLUDE [Customer Id]: MAX([Count orders])})
However, if a specific customer did not make a purchase during a specific month, he is not included in the LOD calculation.
For instance, using my example,
The first cohort is composed of customer #1 and #2. Looking at the tableau workbook attached, in february the value calculated in blue is 3 (maximum value of customer #1 in february). However, I would like tableau to also take 2 (maximum value of customer #2 in january) and then make an average (2+3)/2=2.5. Because customer #2, did not make an order in february, he is not included in the calculation.
I was hoping to calculate this in the following way :
AVG (window_max ({ INCLUDE customer_id : MAX ( Count_orders )}, FIRST(), 0))
But it does not work because window_max has to take aggregate values.
Is there a way to overcome this ?
Thank you for your time and help.
data
tableau worksheet
tableau
tableau
edited Nov 13 '18 at 17:33
Siva
7,64282847
asked Nov 13 '18 at 16:45
Mathieu TardyMathieu Tardy
11
edited Nov 13 '18 at 17:33
Siva
7,64282847
asked Nov 13 '18 at 16:45
Mathieu TardyMathieu Tardy
11
edited Nov 13 '18 at 17:33
Siva
7,64282847
edited Nov 13 '18 at 17:33
Siva
7,64282847
edited Nov 13 '18 at 17:33
Siva
7,64282847
7,64282847
asked Nov 13 '18 at 16:45
Mathieu TardyMathieu Tardy
11
asked Nov 13 '18 at 16:45
Mathieu TardyMathieu Tardy
11
asked Nov 13 '18 at 16:45
Mathieu TardyMathieu Tardy
11
11
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53285747%2ftableau-using-window-max-at-the-customer-level-cohort-analysis%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53285747%2ftableau-using-window-max-at-the-customer-level-cohort-analysis%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
