6 Replies Latest reply: Mar 1, 2016 3:52 PM by Stefan Wühl RSS

    If Statement for Dimension

    Cassandra Baqir

      Right now I have a dimension like this: if(today()>APP_DATE,FY_QTR)

       

      but I want it to only display the FY_QTR if sum(ACT_AMT)>0 for the end of the quarter. Something close to this  maybe?

       

      =if(APP_DATE>$(=Max({<ACT_AMT={'>0'},FY_QTR)))

        • Re: If Statement for Dimension
          Sunny Talwar

          May be something like this:

           

          If(APP_DATE > Max(TOTAL {<ACT_AMT = {'>0'}>} APP_DATE), FY_QTR)

          • Re: If Statement for Dimension
            Clever Anjos

            It would be nicer if you

            • Use only FY_QTR as dimension
            • Create your first expression with your logic, returning 0 or 1
            • Set "Dimension Limits" to narrow your records when 1st expression = 1
            • Re: If Statement for Dimension
              Stefan Wühl

              You need to use advanced aggregation if you want to use aggregation functions per dimension value, maybe something like

               

              =Aggr( If( Today() > APP_DATE and Sum(ACT_AMT) >0, FY_QTR), APP_DATE)

               

              Not sure if this is what you want, just to demonstrate the general approch using aggr() function.

              • Re: If Statement for Dimension
                Cassandra Baqir

                Had some more clarification... I have a variable called vPriorQuarter = date(QuarterEnd(addmonths(max(FCST_DATE),-3)), 'MM-DD-YYYY') .

                 

                I only want to show the quarter in the graph for quarters that have completed - not during the current quarter or future quarters. Right now, for example, I would only expect to see FY16 Q2 and earlier since FY16 Q3 doesn't end until 3/31/16.

                 

                Is =If(APP_DATE < Max(TOTAL {<ACT_AMT = {'>0'}>} APP_DATE), FY_QTR) not the right way to go?

                  • Re: If Statement for Dimension
                    Sunny Talwar

                    Ya less then (<) sign make sense, if that's what you questions is

                    • Re: If Statement for Dimension
                      Stefan Wühl

                      Well, it's hard to answer your questions for me, seems I miss context every time I try to understand what you are requesting.

                       

                      If you want to limit your APP_DATE values to previous FY_QTRs, maybe like

                       

                      If(APP_DATE < vPriorQuarter, FY_QTR)


                      but here,


                      If(FY_QTR < vPriorQuarter, FY_QTR)


                      could work also. And maybe just a dimension FY_QTR and a set analysis filter in your expressions would be better anyway.


                      And if you want to combine both filter, maybe like


                      If(APP_DATE < vPriorQuarter and APP_DATE < Max(TOTAL {<ACT_AMT = {'>0'}>} APP_DATE) , FY_QTR)


                      but again, not sure what you are trying to achieve at all.