5 Replies Latest reply on Feb 13, 2018 5:45 PM by chris guth

    Weight single punch survey data

    chris guth

      I have a measure named GENDER (where 1 = male, 2 = female).  I need to produce a chart of gender applying the variable called WEIGHT. I've accomplished this by creating 2 calculated fields (Male and Female) but seems to me like I should be able to accomplish this with one calculated field since this is a single punch variable. I found an article that referenced a formula:  SUM([WEIGHT]) / TOTAL(SUM([WEIGHT])) but if I enter this in the calc field it just gives me a value of 1.  I'm guessing I need to enter something else???  Any help would be appreciated.  I could stick with the 2 calc fields but I have another single punch var to weight that has 6 options and I'd prefer to do that with a single calc field rather than 6.

        • 1. Re: Weight single punch survey data
          chris guth

          btw..the formula in my 2 separate calc fields are,



          SUM (IF [GENDER]=1 THEN [Weight] ELSE 0 END)

          / SUM (IF ([GENDER]>=1 AND [GENDER]<=3) THEN [Weight] ELSE 0 END)




          SUM (IF [GENDER]=2 THEN [Weight] ELSE 0 END)

          / SUM (IF ([GENDER]>=1 AND [GENDER]<=3) THEN [Weight] ELSE 0 END)

          • 2. Re: Weight single punch survey data
            Okechukwu Ossai

            Hi Chris,

            It will be helpful if you can mockup a sample dataset.


            Did you try wrapping the denominator in TOTAL()?

            For example, Male will become


            SUM (IF [GENDER]=1 THEN [Weight] ELSE 0 END)

            / TOTAL(SUM (IF ([GENDER]>=1 AND [GENDER]<=3) THEN [Weight] ELSE 0 END))


            Without any knowledge about your dataset and the intended final output or viz, you can try combining the calculations into a single field like this;



            This calculation will be performed for each row. However, If you want it fixed per Gender then you can use LOD. Let me know if this is what you are looking for. Consider posting some sample dataset if you require further help.



            • 3. Re: Weight single punch survey data
              Zhouyi Zhang

              Hi, Chris


              Combine your 2 calculation into one should be something similar below


              {Fixed [Gender]:sum([Weight])}


              {Fixed :sum([Weight])}


              Hope this helps



              • 4. Re: Weight single punch survey data
                chris guth

                as I mentioned in my original post, I used the formula SUM([Weight])/TOTAL(SUM([Weight])) and it produced a value of 1 so I must be using it incorrectly...an explanation of how to apply it would be helpful. As for the data set...imagine a data set with 2 fields GENDER which consists of 1's and 2's and a field called WEIGHT which has 3 different value...not directly related to the Gender variable.  I want to produce a chart that applies the weight associated with that respondent to the GENDER associated with that respondent.


                RESP   GENDER  WEIGHT

                1               1               .250

                2               2               .250

                3               1               .500


                So instead of being 67% MALE (1) and 33% FEMALE (2), it would be 50% MALE and 50% FEMALE.  I am able to do this by creating 2 calculated fields, one for weighting MALE and one for weighting FEMALE, I just thought I might be able to accomplish the same thing with a single calculated field since GENDER in a single punch question not a select all that apply question. Hope that makes sense.

                • 5. Re: Weight single punch survey data
                  chris guth

                  OK, I think I figured out how to use the SUM([Weight])/TOTAL(SUM([Weight])) calculated field. I placed the GENDER dimension in the color box and the GenderWgt calculated field in the size box...seems to be working. (fingers crossed)