6 Replies Latest reply on Mar 4, 2016 7:09 AM by Andrew Watson

    How to count selected values in filter

    Pierre Wunderlich

      Hi Together,

       

      I have the following data frame with a filter for the different months (Jan - Feb is filtered):

       

      Item Name
      Revenue Jan
      Revenue Feb
      Revenue per month
      Car100020001500
      House50005000 ???

       

      Now I want to calculate the revenue per month. It works fine for Car but it doesn`t work for House, because there is no revenue in Feb (must be 2500).

      So my question is: How can I count the number of selected months in the filter and use this value in a calculated field to calculate the right revenue per month (even when revenue values are missing)?

      Here it must be 2 (also for House).

      Later I want to have only the column "Item Name" and "Revenue per Month".

       

      I would be very happy if somebody could help me!

       

      Thanks.

        • 1. Re: How to count selected values in filter
          Andrew Watson

          What about COUNTD([MonthField])?

          • 2. Re: How to count selected values in filter
            Vishal Shah

            You should try using the ZN function to replace NULL (blank) value with zero. Once that is done, your average value should be correct.

            • 3. Re: How to count selected values in filter
              Pierre Wunderlich

              Unfortunately it does not work.

              The not aggregated data looks like this:

               

              Month     Item Name      Revenue

              Jan         Car                  1000

              Feb         Car                  2000

              Jan         House              5000

               

              Aggregated it should look like this:

               

              Filter with Months (Jan + Feb selected)

               

              Item Name     Total Revenue     Revenue per Month

              Car                 2500                    1500

              House             5000                    2500 (here is currently displayed 5000)

               

              I am looking forward to more help and a detailed explanation how I can fix it.

              • 4. Re: How to count selected values in filter
                Andrew Watson

                Create a calculated field like this to count the number of months, call it MonthCount: {EXCLUDE [Item Name]:COUNTD([Month])}

                 

                To calculate Rev Per Month your calculation would be SUM([Revenue)/ATTR(MonthCount)

                2 of 2 people found this helpful
                • 5. Re: How to count selected values in filter
                  Pierre Wunderlich

                  Thank you very much, looks very good!

                   

                  But I have one additional question. What is when a new product is introduced  in February.

                  Than it must be divided by 1 not by 2. Do you also have a solution for that?

                  • 6. Re: How to count selected values in filter
                    Andrew Watson

                    That's a completely different requirement and at first glance appears significantly more complex. Effectively what you're asking is:

                     

                    • When a month is after the introduction month (defined by what?) and had no sales - i.e. the record for that product/month combination doesn't exist - the month should be created for the purposes of the average
                    • When a month is before the introduction month - i.e. the record for that product/month combination doesn't exist - the month shouldn't be created for the purposes of the average

                     

                    These requirements are contradictory, you're asking to create a month in some cases and don't in other. This is challenging and, without being able to spend long thinking about solutions, you may be best to create the months that don't exist where you want them to exist in your underlying data, then doing a COUNTD at item level.

                    1 of 1 people found this helpful