10 Replies Latest reply: Feb 24, 2017 11:36 AM by omar bensalem RSS

    Variable fields

    Allan Charles

      I am currently using a variable field to compare sales for a day.

       

      Sum({< Inv_Date = {'$(=vDateCompare1)'}>}

       

      How would I be able to change that value so that it includes for the full week.

       

      All invoices from vDateCompare1 field up to vDateCompare1 + 6

       

      Thanks

        • Re: Variable fields
          Allan Charles

          Sum({< Inv_Date = {'$(=vDateCompare1)'}>} Inv_Invoiced)

           

          Sorry missed the end off the formula

          • Re: Variable fields
            Rajesh RS

            Hi Allan,

             

            You have to create two variables, vStartWeekDate and vEndWeekDate

             

            Then you have to write the set analysis expression with less than(<) and greater than(>) symbols.

             

            Regards,

            Rajesh R. S.

            • Re: Variable fields
              Aar Kay

              May be this:

              Sum({< Inv_Date = {">=$(=vDateCompare1)<=$(=vDateCompare1+6)"}>FIELD}

              • Re: Variable fields
                Allan Charles

                I was thinking about a second variable as it did not like me adding 6.

                 

                I've managed to get it to work however with this. I'm not sure how messy it is.

                 

                sum((IF(inweek(Inv_Date, vDateCompare1, 0), 1, 0))*Inv_Invoiced)

                 

                Thank you for taking the time to reply.

                • Re: Variable fields
                  Andrey Khoronenko

                  Hi Allan,

                   

                  You clearly have a dimension of [Date]. Why do not you want to use to filter sheet date with any manually selection of dates? Another option, make the selection of what you need, make a bookmark and in the expression Set Analisys make a link to this bookmark.

                   

                  Regards,

                  Andrey

                    • Re: Variable fields
                      Allan Charles

                      Hi Andrey,

                       

                      Thank you for taking the time to reply. To clarify I want to be able to obtain a full week's sales so that they can be compared to a different week's sales without the hassle of selecting each date manually which is time consuming for the reps.

                       

                      The InWeek function has allowed me to compare 2 different weeks by entering just one date in the variable field.

                       

                      This then shows the total sales for each customer that week in a column,  puts the previous week in the next table column and calculates the difference in the third so we can see if the sales have increased/decreased week on week.

                       

                       

                       

                      Allan

                        • Re: Variable fields
                          Andrey Khoronenko

                          You want to see the result of weekly or how long the scanning window in a week? Today, February 24, 2017, Friday.

                           

                          1.–°omparison depth - Sunday this week (19.02.2017 or a another day?)?

                          2.Comparison depth - Friday of last week(17.02.2017)?

                           

                          Regards,

                          Andrey

                          • Re: Variable fields
                            omar bensalem

                            If I fully understand; let's assume your variable is vToday=today() (or another date)

                             

                             

                             

                            if it's today: 24/02/2017
                            you want to compare all the week or from the start of the week to today?
                            from 20/02/2017 to 24/02/2017?

                             

                            If that's the case:
                            you can use 2 expressions:

                             

                            actual week to date:sum(  {<   date={">= $(=WeekStart( $(vtoday) )) <= $(vtoday)"}    >} sales)

                            previous week to date: sum(  {<   date={">= $(=WeekStart( $(vtoday) -7) )<= $(vtoday)-7"}    >} sales)

                             

                            if you want to perform comparison for the whole week:

                             

                            actual week :sum(  {<   date={">= $(=WeekStart( $(vtoday) ) )<= $(=WeekEnd( $(vtoday) ))"}    >} sales)

                            previous week : sum(  {<   date={">= $(=WeekStart( $(vtoday) -7) )<= $(=WeekEnd( $(vtoday) -7))"}    >} sales)

                             

                            Hope this will be helpfull,