5 Replies Latest reply: Jul 3, 2013 3:01 PM by Stefan Wühl RSS

    if Statement Set Analysis

    Bradley Coyne

      hi i have the following statement.

       

      what my mission is to do

       

      provide dynamic date selection via variable Från Datum and Till Datum, if there is no Date provided there then take

      the Default selection (i.e Year 2013, month 6 osv)

       

      i have test with hardcoding just a single year, however this seems to overide my first if statement.

       

       

      if(%DatXX>=([Från Datum]) and %DatXX<=([Till Datum]) ,sum([Förs pris] * [Förs-kurs] * [Ursprungligt antal]),

      sum({$<Year ={2013}>}[Förs pris] * [Förs-kurs] * [Ursprungligt antal] ))

       

       

      help please...

        • Re: if Statement Set Analysis
          Stefan Wühl

          Not easy to answer without knowing how all your fields are related in your data model.

           

          Assuming [Från Datum] and [Till Datum] are variables and not fields, maybe like

           

          if( isnum( [Från Datum]) and isnum([Till Datum]) ,

          sum({<%DatXX = {">=$([Fran Datum])<=$([Till Datum])"}>} [Förs pris] * [Förs-kurs] * [Ursprungligt antal]),

          sum({$<Year ={2013}>}[Förs pris] * [Förs-kurs] * [Ursprungligt antal] )

          )

           

          You need to double check that the format of your two dates matches the format of your %DatXX field.

            • Re: if Statement Set Analysis
              Bradley Coyne

              Hi Swuehl!

               

              Thanks for your reply Unfortunately that did not help.

               

              i have attached the application now to the the message so that you can get a better look.

               

              your help is much appreciated.

               

              Best,

              Bradley

                • Re: if Statement Set Analysis
                  Stefan Wühl

                  You just need to correct for my incorrect spelling of your start date in the set expression:

                   

                  =if(isnum("Från Datum") and isnum("Till Datum"),

                  sum({<%DatXX = {">=$(Från Datum)<=$(Till Datum)"}>} [Förs pris] * [Förs-kurs] * [Ursprungligt antal]),

                  sum({$<Year = {2013}>}[Förs pris] * [Förs-kurs] * [Ursprungligt antal] ))

                    • Re: if Statement Set Analysis
                      Bradley Coyne

                      Just a short note , if i would like to pick up the Current Selection (Year,Month,Day as example ) in the Set Analysis statement instead of '2013', how would one do?

                       

                      Thank you for your help.

                      Best,

                      Brad

                        • Re: if Statement Set Analysis
                          Stefan Wühl

                          To let QV consider your selections for the sum() in the ELSE branch of the if() statement, just remove the set expression completely, considering the selection is standard behaviour!

                           

                          But I think you should ignore all calendar fields in the first sum's set expression:

                           

                          if(isnum("Från Datum") and isnum("Till Datum"),

                          sum({<%DatXX = {">=$(Från Datum)<=$(Till Datum)"}, Year, Quarter,Month,Day >} [Förs pris] * [Förs-kurs] * [Ursprungligt antal]),

                          sum([Förs pris] * [Förs-kurs] * [Ursprungligt antal] ))