9 Replies Latest reply: Feb 10, 2011 6:31 PM by Anil Konduri RSS

    No. of days in a month

    Anil Konduri

      Hi All,

      I have date & sales fields YTD... I have to populate my charts with Sales extrapolated for given month...

      for Example: ((Sales/no. of days mtd) * total no. of day in a month)

      i.e. if we have data only from Feb 1 - Feb 8... then ((Sales/8)*28)

       

       

      Thanks in Advance

        • No. of days in a month
          Neil Miller

          Count(distinct Date) should give you the number of days that you actually have, assuming the Month is a dimension. That should give you your 8.

          For your 28, maybe: Day(MonthEnd(Min(Date)))?

          • No. of days in a month
            John Witherspoon

            Is your "Month Yr" field a date field with an underlying value of the first day of the month, and a display format of 'MMM-YY'? Do you have a "Date" field connected to it in the same calendar table? Since you mention February 8 and this is February 8, do you then have data loaded even for the current date, and you consider that data to be sufficient to count today as a full day? Do you count days even if that day has no data? If yes to all of that, maybe this?

            sum(Sales) * if("Month Yr" < monthstart(today()), 1, day(monthend("Month Yr"))/day(today()))

            If no to some of that, perhaps you can clarify the requirement?

              • No. of days in a month
                Anil Konduri

                no its not first day of the month...i have date filed using that I am generating Month, Year, etc.... something like this

                Load

                Date,

                Month(Date),

                Year(Date),

                Account,

                Sales,

                ...

                Resident table;

                 

                For an example I said its Feb 8th but the data available till previous day i.e Feb 7th

                  • No. of days in a month
                    Anil Konduri

                    so my requirement is to estimate the sales based on available data.... i.e if the sales so far (Feb 1 - Feb 7) by month is $700... i have to extrapolate the sales for the whole Feb month.... (700/7)*28 = 2800 for feb... so on.... & on March 1st Feb calc witll be (Sales/28)*28....

                     

                    • No. of days in a month
                      John Witherspoon

                       


                      qlikviewgoer wrote:no its not first day of the month...i have date filed using that I am generating Month, Year, etc.... something like this


                      OK, but you don't show how the "Month Yr" field is defined. So how IS the "Month Yr" field defined? Or are you showing fields in your .bmp that don't even exist? Do you only have a "Month" field and a "Year" field, but no "Month Yr" field?

                      And let's say that on Feb 8 we only have sales for Feb 2, Feb 3 and Feb 6. I assume we STILL want to count that as seven days of sales for extrapolation purposes, correct?

                        • No. of days in a month
                          Anil Konduri

                          for "Month Yr" filed...

                          Load

                          Date,

                          Month(Date),

                          Year(Date),

                          Month(Date)&' '& Year(Date) As [Month Yr]

                          Account,

                          Sales,

                          ...

                          Resident table;

                           

                          Yes, but I am 100% sure that will have sales on all 365 days....

                           

                            • No. of days in a month

                              Count of current days can be calculated from :

                               

                              if

                               

                               

                              and for the total No of days in montn i think u create a inline table with MOnth and No of days in month. and it can be used in expression.

                               

                              ( Month= $(=Max(Month)) , count(Day(Date))) // this will give you current count of days ina month



                              • No. of days in a month
                                John Witherspoon

                                 


                                qlikviewgoer wrote:Month(Date)&' '& Year(Date) As [Month Yr]


                                I would urge you to do this instead:

                                ,date(monthstart(Date),'MMM YYYY') as "Month Yr"

                                Visually, it displays the same. However, it also sorts correctly and allows you to use date functions on it. It's just a much more useful format than a text field. At that point, the only issue I'm seeing with the expression I gave you is that it includes the current day, and you don't want the current day. So use set analysis to exclude it from the sum, and subtract 1 from the number of days in the current month. When you divide by 0 you'll get null, so on the first day, your forecast for the month will be null, but that makes sense to me. Changes highlighted.

                                sum({<Date-={'$(=today())'}>} Sales)
                                *if("Month Yr" < monthstart(today()), 1
                                ,day(monthend("Month Yr"))/(day(today())-1))

                                See attached.