7 Replies Latest reply: Jul 19, 2011 7:21 AM by Stefan Wühl RSS

    Monthtodate()

      Hello Guys,

       

           I want to calculate the total sales of month of selected date....

       

           How can I acheive that..? Is tat possible by using inmonthtodate()   function.?

       

           If yes, can u please give some examples.?

       

           Thanks in advance.

        • Monthtodate()
          Stefan Wühl

          Hi Rajan,

           

          this could be done with set expressions, something like

           

          = sum({<Date= {"<=$(=Max(Date))>=$(=monthstart(max(Date)))"}>} Sales)

           

          for calculating sales month to date.

           

          Regards,

          Stefan

            • Monthtodate()

              hi

               

              Could you explan the above formula indetail.

               

               

              rgds

              senthil

                • Monthtodate()
                  Stefan Wühl

                  Hi Senthil,

                   

                  sum( Sales) sums all Sales within your current selection.

                   

                  If you want to limit the date range (i.e. like selecting certain dates without actually having the user to select them), you can use so called set analysis with a set expression. Thats the part:

                   

                  {<Date= {"<=$(=Max(Date))>=$(=monthstart(max(Date)))"}>}

                   

                  I assumed that you have a something like a Date dimension to select date from, right?

                   

                  I limited the dimesion Date to values <= Max(Date) and >= monthstart(max(Date))

                   

                  I used max(Date) to get the most current date within the selection (if you select only one date, then that's Max(date), but if you select more than one date, you get the most current one).

                   

                  e.g. if you select today (2011-07-19) the set expression would be in literal

                  {<Date= {"<=2011-07-19>=2011-07-01"}>}

                   

                  (At least that's what I intended) So you get the date range for MonthToDate.

                   

                  (Also, the $(= is to evaluate QlikView functions inside the set expression)

                   

                  Hope this helps.

                   

                  Is my expression correct for your requirement?

                   

                  Regards,

                  Stefan

                • Monthtodate()

                  Hi,

                   

                       Can we achieve this without using SET ANALYSIS?

                   

                   

                       Using if statement or someother function?

                    • Monthtodate()
                      Stefan Wühl

                      Well, you already suggested

                       

                      InMonthToDate ( date, basedate , shift )

                       

                      So, without trying myself, maybe:

                       

                      sum(if(InMonthToDate(Date, max(Date),0),Sales))

                       

                      But this function would be quite depending on actual selection, i.e. if you select only one date, you get only the sum of this date and not the (not selected) sales prior this date within the month. You should get a correct result, if you select all dates up to the Date to evaluate.

                       

                      Could you post a sample application?

                       

                      Regards,

                      Stefan

                        • Monthtodate()
                          Stefan Wühl

                          me again,

                           

                          you could also put the MTD calculation in the script, assuming you don't need to take care of Sales selection afterwards.

                           

                          So if you have a SalesTable with unique Date field and Sales field,

                           

                          you could try

                           

                          MTDSalesTable:

                          LOAD

                          Date,

                          if(Month(Peek('Date')=Month(Date),peek('MTDSales')+Sales,Sales) as MTDSales

                          resident SalesTable order by Date;

                           

                          But I think this is only a solution for simple data structures.

                           

                          Regards,

                          Stefan

                            • Re: Monthtodate()
                              Stefan Wühl

                              If {1} works for you, you could also try to set a variable to the selected Date (varDate=max(Date))

                               

                              and try (without trying myself) something like:

                               

                              sum({1} if (Date>=monthstart($(varDate)) and Date <=$(varDate), Sales))

                               

                              or sum({1} if(InMonthToDate(Date,$(varDate),0), Sales))

                               

                              but this also disregards other selections.

                               

                              Stefan

                               

                              Nachricht geändert durch swuehl