Monthtodate()

[#1]

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.

[swuehl]

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

[#3]

hi

Could you explan the above formula indetail.

rgds

senthil

[swuehl]

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

[#5]

Hi,

     Can we achieve this without using SET ANALYSIS?

     Using if statement or someother function?

[swuehl]

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

[swuehl]

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

[swuehl]

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