6 Replies Latest reply on Oct 5, 2017 8:08 AM by KAREN LUNA

    how to count a calculated field.

    KAREN LUNA

      Hello!

      I have a problem trying to count a calculated field.

      I have list of clients and their balance at the end of each month.

      I only need to know the last month balance and be able to compare it to the month before.

      So I did a parameter for the months of the year.

       

      Selected month  (in the parameter)

      IF MONTH([Fecha]) = [FECHA CANTIDAD OTORGADA SELEC] THEN [Saldo Vigente Prom] END

       

      Month before the selected one:

      IF MONTH([Fecha]) = [FECHA CANTIDAD OTORGADA SELEC]-1 THEN [Saldo Vigente Prom] END

       

      Whith this i was able to get the correct balance for each month and Works wonderful.

      After I did a calculated fiel to be able to tell wich cliente balance whent up and wich went down or stayed the same.

       

      IF SUM([SELECTED MONTH])  <  SUM([MONTH BEFORE SELECTED])

          THEN "DOWN"

      ELSEIF SUM([SELECTED MONTH])  >  SUM([MONTH BEFORE SELECTED])] )

          THEN "UP"

      ELSEIF SUM([SELECTED MONTH])  = SUM([MONTH BEFORE SELECTED])    THEN "NO MOVEMENT"

      ELSE "NA"

      END

       

      This works to… the problem comes when I want to group the results by city, so I can see how many clients when up, down or without movement in the last month by each city. When I try to do that this happens:

      For some reason it sum evrything on only one option UP (ALZA) or DOWN (BAJA)

      how can i be able to count this field?

      Captura.PNG

        • 1. Re: how to count a calculated field.
          Deepak Rai

          The Solution Lies in LOD for this. Please attach a workbook.

          Thanks

          Deepak

           

          Something Like This

           

          {FIXED City: SUM(

          IF ([SELECTED MONTH])  < ([MONTH BEFORE SELECTED])

              THEN 1

          ELSEIF ([SELECTED MONTH])  >  ([MONTH BEFORE SELECTED])] )

              THEN 1

          ELSEIF ([SELECTED MONTH])  = ([MONTH BEFORE SELECTED])    THEN "NO MOVEMENT"

          ELSE 1

          END)}

          • 2. Re: how to count a calculated field.
            KAREN LUNA

            Hellow Rai!

            i try what you say but i cant make it work...

            i cant attached the workbook because i work at a bank.. but i made up a mockup but i cant find the way to attached it in here :S

            • 3. Re: how to count a calculated field.
              Deepak Rai

              You can attach in main thread , not in email. Pl go to main thread of your question.

              • 4. Re: how to count a calculated field.
                KAREN LUNA

                Thanks! i attached the example

                • 5. Re: how to count a calculated field.
                  Joe Oppelt

                  I did this with table calcs.  I think you c an also do it with LODs.

                   

                  In the attached I made two table calcs and added them to your sheet 1.  [Total UP] and [Total DOWN].  (If there really will be a N/A value to count, you can model a third calc soing the same thing I did in those.)

                   

                  If you right click on either of those calcs on the TEXT shelf and do "Edit Table Calc" you will see that I'm running those calcs along the dimensions on the sheet, and restarting every CITY.  I added these to TEXT, and formatted the text editor to label the different values for you.  You can see that I'm getting the correct values across the city, even though the numbers are repeated for each [Num linea].

                   

                  Go to Sheet 3.  Here I dragged most of the stuff from rows into the detail shelf.  This is just an intermediate step.

                   

                  Sheet 4:  You will notice that I created a new calc called [Index] and put it on the filter shelf.  Drag it off and see what happens.  You get nultiple occurrences of the values within each city.  That's because there are as many occurrences of the values as there are for [Num linea] within each city.  So hit the back-arrow so that the filter goes back on the sheet.  What this is doing is just grabbing the first occurrence of the values for each [City].  (You'll see that I have edited this calc to do the same settings as the table calcs for the totals.)  Since the values are the same on each [Num linea] row, we really only need to see one.  And yes, we have to have [Num linea] on the sheet because that's where the counting occurs.

                   

                  Go to Sheet 5.  I changed the display to use MeasureNames/MeasureValues to display these calcs.  Just to show a different way of displaying these.

                  1 of 1 people found this helpful
                  • 6. Re: how to count a calculated field.
                    KAREN LUNA

                    Thankyou so very much!!! it works perfectly!