11 Replies Latest reply: Nov 23, 2017 10:41 PM by Luis Madriz RSS

    Weekday Sales

    Vivek Ranawat

      Hello All,

       

      The problem what am facing is to report Last day sales on Monday. For rest of the days to report sales for the previous day is working fine.

      I have use the formula "=IF((Today()- [Trans Date])<=1,'OK')" and  i have untick box Include Null Value. This means if anything more than 1 day will be blank in the table.

       

      The only problem is to report Sales on Monday where the previous day is Sunday. The solution what am looking for now is to get the Sales Table for the Friday. So on 20th Nov table should show data of 17th November that is Friday.

       

        

      Transaction DatesDays
      11/14/2017Tuesday
      11/15/2017Wednesday
      11/16/2017Thursday
      11/17/2017Friday
      11/20/2017Monday
      11/21/2017Tuesday
      11/22/2017Wednesday

       

      I cannot select the Max date option, the reason is that if there are No Sales for the previous day still table will have data of Max Date Sales. The final requirement is Table should be blank if there are No Sales for the Previous date.

        • Re: Weekday Sales
          Luis Madriz

          Hi Vivek,

           

          Maybe you want to complement your IF statement with a test of the WeekDay?

           

          Not sure if you're trying to do this in a visualisation or in a load script,

           

          Cheers

           

          Luis

          • Re: Weekday Sales
            Luis Madriz

            Hi,

             

            Just wondering to know how you went about this one,

             

            Cheers,

             

            Luis

             

            PS. When applicable please mark the appropriate replies as Correct. This will help community members know which discussions have already been addressed and have a possible known solution. Please mark replies as Helpful if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as Helpful if you feel additional info is useful to others

            • Re: Weekday Sales
              Vivek Ranawat

              Hi Luis,

              Still, i haven't found any perfect solution to my problem.

              I tried the below IF Statement, the only problem is the previous Fridays sales also get opened.

              =if(WeekDay(([Trans Date]))='Fri','OK',(IF((Today()- [Trans Date])<=1,'OK')))

               

              Hope get this resolved at earliest.

                • Re: Weekday Sales
                  Luis Madriz

                  Hi,

                   

                  As you mentioned that your original IF was working except for Mondays, then just confirm if it's Monday and make the adjustment ...

                  Please try with this: =if(Today()-[Trans Date]-IF(Num(WeekDay(Today()))=1,2,0)<=1,'OK')


                  Cheers,

                   

                  Luis

                   

                  PS. When applicable please mark the appropriate replies as Correct. This will help community members know which discussions have already been addressed and have a possible known solution. Please mark replies as Helpful if the provided solution is helpful to the problem,

                • Re: Weekday Sales
                  Vivek Ranawat

                  Hi Luis,

                   

                  In your IF statement is 0 has been defined as Monday?

                  • Re: Weekday Sales
                    Vivek Ranawat

                    Hi Luis,

                     

                    Thanks for your continuous support. But the problem is not yet resolved.

                     

                    For Today report Sales figures (Only should reflect yesterday- Tuesday Sales) but Monday sales figures also getting included.

                    • Re: Weekday Sales
                      Vivek Ranawat

                      Hi Luis,

                       

                      I have tried using the last formula. But still, the result is same.

                       

                      Thanks for your continued support.

                        • Re: Weekday Sales
                          Luis Madriz

                          Hi Vivek,

                           

                          I had an error thinking that Monday was 1 when in reality is 0 when I use the second parameter of Weekday as 0. Also as my data had sales today (24/Nov) I decided to make sure that it'll only show the day before or Friday if today was Monday, in so for that I'm using Floor and =1 instead of <=1

                           

                          So this worked for me, please see screenshot below. The second table gets automatically restricted to yesterday (or Friday if it is Monday).

                           

                          The if changed to this:

                          =IF(Floor(Today())-Floor([Trans Date])-IF(Num(WeekDay(Today(),0))=0,2,0)=1,'OK')

                           

                          And if you use it with a Measure as a Set Analysis it could look like this:

                          Sum({<[Trans Date]={"=Floor(Today())-Floor([Trans Date])-IF(Num(WeekDay(Today(),0))=0,2,0)=1"}>} SalesAmount)

                           

                          I hope this helps,

                           

                          Cheers,

                           

                          Luis

                           

                          Untitled.png