8 Replies Latest reply on May 6, 2018 7:39 AM by Jennifer VonHagel

    countd or calculated field partial problem

    Ramiro Sanchez Fay

      Hi, i have a issue. I´m working in crime statistics. I have a region field (provincia), crime field (delito). I need to show the crime trend per month only when in this month have values for all regions (diferent of zero or null), because i need total crime per month per all regions. This issue could solved it, but when I select only one region or two, does not work, because nothing shows.

       

      I attach a workbook, in "NOW CRIME ALL REGIONS" with the partial solve, but adition when i select one or two regions (with filter), need to show the crimes.

       

      Thanks a lot

      Ramiro

        • 1. Re: countd or calculated field partial problem
          Jennifer VonHagel

          ***This message was edited. Some intermediate steps were missing when I posted the original.***

           

          Hi Ramiro, I'm not sure I understand what you're trying to do.

           

          Do you want to only allow months in which every province has at least one crime?  So here, do you want to exclude all the months in 2016 because province Salta has no data?

           

           

          I created this filter. First, it finds for each month and Province the sum of Cantidad. The ZN() turns null values to 0.  Then, for each month, I find the min Cantidad. since this is already summed at the province level, if there is a min which is equal to 0, it means this month has a province having cantidad of 0. So I test that the min Province Cantidad must be greater than 0.

          { FIXED DATE(DATETRUNC('month',[Fecha])) :

              MIN({ FIXED DATE(DATETRUNC('month',[Fecha])), [Provincia] : SUM(zn([Cantidad])) })

          } > 0

           

          You can see the values in the table:

           

          If I apply this filter to your worksheet, I can select a single province and the chart still works:

          Hope this helps. If I misunderstood and you have a different question, let me know .

           

          Thanks,

          Jennifer

          • 2. Re: countd or calculated field partial problem
            Ramiro Sanchez Fay

            Thanks Jennifer, almost there... I need to show total crime per region. If i select one region i need to show the crime. For example:

             

             

            If i select only "Catamarca" region, i need to show all crimes and all data (2016 y 2017), because i have data for all month.

             

             

            If i select only "La Rioja" region  i need to show "hurto" crime for 2016 and 2017, and "robo" crime only for 2017, because i have complete data for 2017 but no for 2016.

             

             

            If i select three regions i need to show only "robo" crime in 2017, because Salta does not have data for "hurto" in 2017.

             

             

            thanks a lot

            • 3. Re: countd or calculated field partial problem
              Jennifer VonHagel

              Ah, I think I see. One more question: which months should show when All Regions is selected - all months or only 2017?

               

              But try this. Go into Edit of your Month(Fecha) filter:

              Go to Special, choose Non-Null Dates:

               

              Now: Catamarca

              La Rioja

              Salta

              All Regions

              • 4. Re: countd or calculated field partial problem
                Ramiro Sanchez Fay

                Thanks again Jennifer. One step more. which months should show when All Regions is selected - all months or only 2017? In this case only 2017, because for all regions, only have values in "robo" crime in 2017:

                 

                in resume when i have one value in zero o null in a month, that month "hide" for a crime even if another region have a value.

                 

                thanks a lot

                • 6. Re: countd or calculated field partial problem
                  Jennifer VonHagel

                  Ok, give this one a try. fingers crossed .

                   

                  I cleared the date filter - though I don't think it matters one way or the other.  I added this filter set to True:

                  MIN(

                      { INCLUDE DATETRUNC('month',[Fecha]), [Delito] :

                      MIN({ INCLUDE DATETRUNC('month',[Fecha]), [Delito], [Provincia] :

                              SUM(zn([Cantidad]))

                          }) > 0

                      }

                  )

                   

                  1. The inner INCLUDE finds the sum of Cantidad for each month-crime-region combination, and returns 0 if cantidad is null.

                  2. The outer INCLUDE finds the MIN region cantidad for each month and crime. If the min = 0, that means there exists for this month and crime a region with 0 cantidad, and we want to exclude this month-crime combination. For this reason, we say that each month-crime combination must have a min province quantity which is > 0.

                   

                  I am struggling to explain it, but hopefully it works .

                   

                  Jennifer

                  2 of 2 people found this helpful
                  • 7. Re: countd or calculated field partial problem
                    Ramiro Sanchez Fay

                    Jennifer... you are the best.... it´s works perfect! i need learn LOD urgently...

                     

                    Happy weekend.


                    Ramiro

                    • 8. Re: countd or calculated field partial problem
                      Jennifer VonHagel

                      You're welcome, glad it helped!