Percentage value not showing correctly in one of the series.....

user06375
user06375 Member
in Charting

Hi,

 

Need help with card building.

 

As you can see in below screenshot, there are 3 series of values which shows %rate. But the last one (conversion rate) is appearing as 100% which is actualy is in the range of 1-2%. I tried changing all formats (percentage, number, default... check box - divided by 100)

 

2017-07-22_15-50-49.jpg 

Best Answer

  • Tomo
    Tomo Contributor
    Answer ✓

    Try "ELSE 0"

     

    SUM((CASE WHEN (`Segment Name` = 'Conversion Activities - Overall') THEN `Unique Visitors` ELSE 0 END )) / SUM((CASE WHEN (`Segment Name` = 'Overall') THEN `Unique Visitors` ELSE 0 END ))

     

Answers

  • Tomo
    Tomo Contributor

    Isn't null using an included column for the denominator by an arithmetic expression of BEAST MODE?

    Won't a chart, but a table be 100%?

     

    BEAST MODEの計算式で、分母にnullが含まれるカラムを利用していませんか？

    また、グラフではなく表でも100%になりますか？

  • user06375
    user06375 Member

    this is the beast mode which pulling conversion rate:

     

    (SUM((CASE  WHEN (`Segment Name` = 'Conversion Activities - Overall') THEN `Unique Visitors` END )) / SUM((CASE  WHEN (`Segment Name` = 'Overall') THEN `Unique Visitors` END ))) 

  • ST_-Superman-_
    ST_-Superman-_ Coach

    I think that you have a misplaced parenthesis in your equation.  Try this instead:

     

    (SUM((CASE  WHEN (`Segment Name` = 'Conversion Activities - Overall') THEN `Unique Visitors` END ))) / (SUM((CASE  WHEN (`Segment Name` = 'Overall') THEN `Unique Visitors` END )))


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • user06375
    user06375 Member

    Thanks Scott for digging... but this also didnt helped :(

  • ST_-Superman-_
    ST_-Superman-_ Coach

    Perhaps we need to look at the data to get a better picture of what is going on here.  You have a column called `Segment Name` which contains the values "Conversion Activities - Overall" and "Overall".  

     

    In your equation, we search for the value "Conversion Activities - Overall" and when we find that we sum the `Unique Visitors` column.  This tells us how many visitors fell into the "Conversion Activities - Overall" segment.  

    We then look at the same segment column for the value "Overall" and when we find that we sum the `Unique Visitors` column.  In this case, I'm not sure that the divisor is really capturing all of the visitors from every segment. 

     

    Maybe try something like this?

     

    (SUM((CASE  WHEN (`Segment Name` = 'Conversion Activities - Overall') THEN `Unique Visitors` END )) / SUM(`Unique Visitors`) 


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • user06375
    user06375 Member

    I'm trying to achieve conversion rate for a particular page of a portal. To get this we divide conversion activity unique visitors by overall unique visitors of portal (achieving this by ETL data).

    I successfully created 6-7 cards similar type for other pages with same beast mode applied, but not sure why in this card conversion rate coming as 100%. I want to remove extra 00% bcoz correct value are mostly 1% troughout the months (except 2% in one or two months). I checked in previous cards and nothing found any thing different, this is realy weird :(

  • ST_-Superman-_
    ST_-Superman-_ Coach

    can you look at a data table to check the values of those fields to make sure they are correct?

     

    Or you could create a sumo card to check the sum of unique visitors when the segment is Overall and when it is a conversion.

     

    It could be an issue with the data and not one with your beast mode.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • Tomo
    Tomo Contributor
    Answer ✓

    Try "ELSE 0"

     

    SUM((CASE WHEN (`Segment Name` = 'Conversion Activities - Overall') THEN `Unique Visitors` ELSE 0 END )) / SUM((CASE WHEN (`Segment Name` = 'Overall') THEN `Unique Visitors` ELSE 0 END ))

     

This discussion has been closed.

Categories