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


      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.




            • Monthtodate()



              Could you explan the above formula indetail.





                • 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?




                • Monthtodate()



                       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?




                        • 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





                          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.




                            • 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.




                              Nachricht geändert durch swuehl