Hello,
We have to create a calculation where we can get the close rate of each brand we have in data
1) We have a fields which gives us the brands selected :-
BrandConsideredDetail_Multi
BrandConsideredDetail_Multi___1
BrandConsideredDetail_Multi___2
BrandConsideredDetail_Multi___3
2) We have a field which gives us the brand actually bought from the selected:-
Brand_detail
3)We have to create a logic of close rate as per the screenshot attached
In this example, Bosch has a Close Rate of 34% (as per attached screenshot)
What we are looking to do is be able to list out all the Brands’ Close Rates, without having to manually enter each brand in a formula and without having to put every other brand in a table
Close Rate Logic :-
What we are looking to implement in QuickSight is :-
Current Logic i am using is :-
Count of Brands Considered
countIf({BrandConsideredDetail_Multi}, {BrandConsideredDetail_Multi} <> ‘’)+ countIf({BrandConsideredDetail_Multi___1}, {BrandConsideredDetail_Multi___1} <> ‘’)+ countIf({BrandConsideredDetail_Multi___2}, {BrandConsideredDetail_Multi___2} <> ‘’)+ countIf({BrandConsideredDetail_Multi___3}, {BrandConsideredDetail_Multi___3} <> ‘’)
Count of Brands Bought (Only When Considered)
countIf({Brand_Detail},
{Brand_Detail} = {BrandConsideredDetail_Multi}
OR {Brand_Detail} = {BrandConsideredDetail_Multi___1}
OR {Brand_Detail} = {BrandConsideredDetail_Multi___2}
OR {Brand_Detail} = {BrandConsideredDetail_Multi___3}
)
We are not getting expected numbers by above fields.
Thanks,
Nikhil
Hi @Nikhilburhade
I guess the Brand Detail represents your Brand bought. The Brand Considered on different rows could have Bosch but the Brand Bought could be different.
But your formula looks to use Brand Detail on the row to compare the Brand Considered as well which may not get you the result you want.
The Brand has to be indepedent and the compute Brand Bought and Brand Considered indepently and then match with the brand list.
This video should help you get an idea on how to approach this issue
Regards,
Giri
Regards,
Giri
Hello @Giridhar.Prabhu
Thanks For the response.
Above video was informative.
Now -I have new calculated fields
1) Bought
sum(
ifelse(
{BrandConsideredDetail_Multi} = {Brand_Detail} OR
{BrandConsideredDetail_Multi___1} = {Brand_Detail} OR
{BrandConsideredDetail_Multi___2} = {Brand_Detail} OR
{BrandConsideredDetail_Multi___3} = {Brand_Detail},
1,
0
)
)
2) Considered
count({BrandConsideredDetail_Multi})+count({BrandConsideredDetail_Multi___1})+count({BrandConsideredDetail_Multi___2})+count({BrandConsideredDetail_Multi___3})
But now we are getting close rate (bought/considered) as 25% for all the brands.
Below is the example.
Please guide further.
Thanks,
Nikhil.
I may need some simulated data to review this. Are you able to upload the simulated data to Arena?
Hello @Giridhar.Prabhu
Looks like we are closer to our goal
1) We have brand bought field which is working fine for us -
ifelse(
{Brand_Detail} = {BrandConsideredDetail_Multi} OR
{Brand_Detail} = {BrandConsideredDetail_Multi___1} OR
{Brand_Detail} = {BrandConsideredDetail_Multi___2} OR
{Brand_Detail} = {BrandConsideredDetail_Multi___3},
qty,
0
)
This is giving us the exact results as expected (brands bought)
qty :- Essentially the “units” or “purchases” count from the survey data
2) Now we have to sort the brand selected/brand considered field as shown in the attached screenshot - we are looking to match the highlighted number.
Below is the fields behind the highlighted numbers :-
ifelse(
contains({BrandConsideredDetail_Multi}, ${BrandDrawClose1}) OR
contains({BrandConsideredDetail_Multi___1}, ${BrandDrawClose1}) OR
contains({BrandConsideredDetail_Multi___2}, ${BrandDrawClose1}) OR
contains({BrandConsideredDetail_Multi___3}, ${BrandDrawClose1}),
“Y”,
“N”)
Instead of using parameter what we are trying to do is : -To list out all the Brands’ Close Rates, without having to manually enter each brand in a formula (too many) and without having to put every other brand in a table.
Close rate is :- Bought/considered
A way to summarize “Close” rate: The % (qty) of respondents who considered Bosch (BrandConsideredDetail_Multi) then purchased Bosch (Brand_Detail)
As per attached example close rate for Bosch is - (41 of 107 is 38%)
Hello @Giridhar.Prabhu
Summarizing above issue in more simpler way :-
A] Brand Bought :- We are getting correct value of Brand_Bought by below calculated field :-
ifelse(
{Brand_Detail} = {BrandConsideredDetail_Multi} OR
{Brand_Detail} = {BrandConsideredDetail_Multi___1} OR
{Brand_Detail} = {BrandConsideredDetail_Multi___2} OR
{Brand_Detail} = {BrandConsideredDetail_Multi___3},
qty,
0
)
{qty} - is essentially the “units” or “purchases” count from the data
{Brand_Detail} - Contains whatever the survey participants answered as the Brand they bought
{BrandConsideredDetailMulti},{BrandConsideredDetailMulti_1}{BrandConsideredDetailMulti_2},{BrandConsideredDetailMulti_3} -Contains whatever the survey participants answered as the Brand they selected/considered but not necessarily bought
B] Brand Considered :- But Now what we are trying to do is :-
In the attached example as samsung is coming at 5 times (in columns :- BrandConsideredDetailMulti,BrandConsideredDetailMulti_1,BrandConsideredDetailMulti_2,BrandConsideredDetailMulti_3) so what we are trying get is :-5 in the “Considered quantity” column.(it might be vary as we are counting Units(qty) and not instances)
Then
C] Close Rate -
Bought/Considered
Filters we are using is :- Product and Time period.
Hope Above image will give the better idea of what we are looking for.
Regards,
Nikhil.
Hi Nikhil,
This requires a bit of experimentation since you are trying to match the value of Brand Detail in a row vs BrandConsidered* columns across all rows in your dataset.
While producing the count by Brand from the BrandConsidered* columns is not a big deal the matching it to BrandDetail after grouping that is the hard part.
I will try this out and let you know.
Regards,
Giri
2 Likes
Hello @Giridhar.Prabhu
Thank you so much for your response.
Please let me know.
Regards,
Nikhil
Hello @Giridhar.Prabhu,
Do we have any update on this issue ?
Regards,
Nikhil.
Sorry! I have been tied up with my projects. I haven’t been able to look at this.
Hello,
We have created a dataset level field by using below SQL query for brand considered/selected so we are good with this.
Regards,
Nikhil.
1 Like
