7 Replies Latest reply on Dec 11, 2018 8:49 AM by Don Wise

    How to get count of a dimension based upon other dimension

    Shahroz Ahmed

      Hello Guys,

       

      I have got this requirement where I have following data. Basically it's snap shot of data, Data is huge which I also attached for reference

      Now Business user expect this following result

      If anyone can help me in getting above result, that would be helpful

        • 1. Re: How to get count of a dimension based upon other dimension
          Jim Dehner

          So what did you try - please attach your twbx workbook with the data to show us how far you got

          thanks

          Jim

          • 2. Re: How to get count of a dimension based upon other dimension
            Shahroz Ahmed

            Hi,

            I am fairly new in tableau so I couldn't do much. I have merely reached to get distinct count of Disc ID per month.

            • 3. Re: How to get count of a dimension based upon other dimension
              Jim Dehner

              see the attached

               

              is this what you wanted to see?

               

               

              Jim

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

              • 4. Re: How to get count of a dimension based upon other dimension
                Shahroz Ahmed

                Jim,

                 

                It's not correct. I mean it's not what I expect in outcome. In your outcome for January 2014 is 26 which should be 32 which I am counting on basis of distinct DiscID per month. That's how it should be counted for every month from Jan to December. I don't need show all year of data we district for example for just year 2014.

                 

                2)The next measure I would like to show in another row is show me count of distinct ID where appr_status is "Returned" but count of appstatus should also be 1 (meaning --- any discid can not have multiple time Returned". If the do then in this case it should be added into disctinct count of DisID.

                 

                3) Third measure in 3 row I want to show count of distinct ID where appr_status is "Returned" but count of appstatus should also be 2 not less 2 or more than 2 (meaning --- any discid not have multiple time Returned". if a disc id have return twice then it should be consider 1 discID and so on it should all discIDs and give the total count).

                 

                4)Fourth measure in 4 row I want to show count of distinct ID where appstatus is "Returned" but count of appr_status should also be 2 not less 2 or more than 2 (meaning --- any discid not have multiple time Returned". if a disc id have return twice then only it should be consider 1 discID and so on it should check all discIDs and get the distinct of  disc ID where appr_status is returned 2 times only).

                 

                5)Five measure in 5 row I want to show count of distinct ID where appstatus is "Returned" but count of appr_status should also be 3 not less 3 or more than 3 (meaning --- any discid not have multiple time Returned". if a disc id have return three times then only it should be consider 1 discID and so on it should check all discIDs and get the distinct of  disc ID where appr_status is returned 3 times only).

                 

                 

                6)Six measure in 6 row I want to show count of distinct ID where appstatus is "Returned" but count of appr_status should also be 4 not less 4 or more than 4 (meaning --- any discid not have multiple time Returned". if a disc id have return four times then only it should be consider 1 discID and so on it should check all discIDs and get the distinct of  disc ID where appr_status is returned 4 times only).

                 

                 

                7)Seven measure in 6 row I want to show count of distinct ID where appstatus is "Returned" but count of appr_status should also be 5 ore more (meaning --- any discid not have multiple time Returned". if a disc id have return 5 times or more then only it should be consider 1 discID and so on it should check all discIDs and get the distinct of  disc ID where appr_status is returned 3 times only).

                 

                 

                same it goes for level 1, 2, 3 and 4

                 

                 

                                        

                Comment(not   to include in report)
                Show count of Distinct "Disc   Ids" in the extract in a given month of the year
                show count of Distinct "Disc   Ids"  RETURNED ONCE
                show count of Distinct "Disc   Ids"  RETURNED TWICe
                show count of Distinct "Disc   Ids"  RETURNED Three times
                show count of Distinct "Disc   Ids"  RETURNED Four Times
                show count of Distinct "Disc   Ids"  RETURNED > 5= Times
                show count of Distinct # of records where   approved by Level = 1
                show count of Distinct # of records where   approved by Level = 2
                show count of Distinct # of records where   approved by Level = 3
                show count of Distinct # of records where   approved by Level = 4
                • 5. Re: How to get count of a dimension based upon other dimension
                  Don Wise

                  Hello,

                   

                  Not sure if the following and attached 2018.3 workbook meets your needs.  I did use Jim Dehner calculation as basis for some of the ensuing calc's so yes I believe his response was correct with regard to getting the proper methodology for you.

                   

                  I expanded it to meet your formatting requirements.  Thx, Don

                   

                  Screen Shot 2018-12-10 at 3.02.24 PM.png

                  • 6. Re: How to get count of a dimension based upon other dimension
                    Shahroz Ahmed

                    Don,

                     

                    I really appreciate for your response. But did you really check the count because I cross checked few which didn't match the count

                     

                    in pic for January you showing count of distinct id 39 who has count of app_status equals 1

                     

                    the result I get from analysis in excel is 14

                    One more example, as your result show 157 count of distinct discID where has count of app_status equals 1

                    my analysis shows 63

                    • 7. Re: How to get count of a dimension based upon other dimension
                      Don Wise

                      Hello Shahroz,

                      It would've been great if your expected output had been provided for originally.  Thank you.  Therefore with the newly provided information, here are the modified results matching your requirements. Thx, Don

                      Screen Shot 2018-12-11 at 8.44.28 AM.png