7 Replies Latest reply on Aug 2, 2018 8:39 PM by tian.yang.4

    calculation help

    jon rios

      here is a screen shot. i i want to isolate the highlighted number, i assumed this formula would do it but it doesnt, what am i doing wrong?

       

        • 1. Re: calculation help
          Ken Flerlage

          Any chance you can share some sample data or a packaged workbook? It's difficult to diagnose this problem without them, unfortunately.

          • 2. Re: calculation help
            meenu choudhary

            Hi Jon,

             

            I have tried above scenario on superstore data and it is giving me correct result.

             

            if ([Category])="Furniture" and ([Sub-Category])="Tables"

            THEN "FALSE" else "TRUE"

            end

             

            • 3. Re: calculation help
              tian.yang.4

              Hi Jon

               

              you can try this way, i use sample-superstore

               

              Picture.png

              • 4. Re: calculation help
                jon rios

                thanks, I should of been more specific, by isolate, i mean, be able to bring 1 calculation to the shelf as a measure and it shows this particular value. So i assume it has to be a {FIXED} expression, but i have tried every combination and cant get this .1410, if I use this expr.  i get Null

                 

                {FIXED [Period Date], [Month Qtr], [Benchmark + Portfolio (RS)], [SegmentLabel1], [SegmentLabel2]:

                sum (if [SegmentLabel1]= "Now 2 CPD" and [SegmentLabel2] = "Were 1 CPD" THEN [DeltaMetrics]

                END ) }

                 

                i assumed if i replace this Measure (in this screenshot), with my calculation, i would be the .1410 and nothing else, BUT everything goes blank (NULL)

                 

                i tried this adding more filtering conditions, but my quarter date part is incorrect...

                 

                {FIXED [Period Date], [Month Qtr], [Benchmark + Portfolio (RS)], [SegmentLabel1], [SegmentLabel2]:

                sum (if [SegmentLabel1]= "Now 2 CPD" and [SegmentLabel2] = "Were 1 CPD"

                        and DATEPART('year',[Period Date])= 2017 and DATEPART('quarter', [Period Date]) = Q3

                        and [Month Qtr]= "Q" THEN [DeltaMetrics]

                END) }

                 

                • 5. Re: calculation help
                  Zhouyi Zhang

                  Hi, Jon

                   

                  It is not easy to give a straight forward advice without seeing a sample workbook.

                   

                  But please try below

                   

                  Hope this helps, otherwise, please prepared a sample workbook, it doesn't have to be your real data, just some make up.

                   

                  ZZ

                  • 6. Re: calculation help
                    tian.yang.4

                     

                    hi Jon,

                     

                    I use your formula and it works.

                    I think the problem is  you cannot fix the column which is a measure--[Benchmark+Portfolio (RS)]

                    {fixed+dimension:sum(measure)}  the dimension determines at what level measure should be aggregated.

                     

                    if you problem is still there maybe you can send me the workbook.

                     

                    YT

                    • 7. Re: calculation help
                      tian.yang.4

                       

                      hi Jon

                       

                      i misread the Benchmark as a measure >_<

                       

                      i build a similar vision and it works

                      The little different here is the Date , maybe you can first delete it and see whether it works and then put it back.

                      Datepart or Datetrunc it has sequence

                       

                      i hope this time your problem can solve.

                       

                      YT