No. of days in a month

[#1]

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

[#2]

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

[johnw]

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?

[#4]

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

[#5]

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

[johnw]

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?

[#7]

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

[#8]

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

[johnw · Accepted solution]

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.

[#10]

Thanks a lot John...