Compare Yearmonth with selected yearmonth

[#1]

From my datatable I have created a field YEARMONTH:

year(Invoicedate) & num(Month(Invoicedate)) as YEARMONTH

I have created a Calendar table with a field CalendarMonthYear:

year(Date) & num(Month(Date)) as CalendatMonthYear.

I use a selectionlist on CalendarMonthYear and  Set the GetFieldSelections(CalenderMonthYear).

Now the question is: I want to know from the Purchase table when I have selected a Yearmonth, the total Quantity purchased before the selected Yearmonth.

I allready have this in the Expression of the 'Quantity before':

sum({<YEARMONTH={"<$(CalendarMonthYear)"}>}Quantity)

But this gives me the total Quantity Purchased. What is the correct syntax for this calculation?

[maneshkhottcpl]

Hi

You should try this

year(Invoicedate) * 100 + num(Month(Invoicedate)) as YEARMONTH

and

year(Date)*100 + num(Month(Date)) as CalendatMonthYear

Good Luck

Manesh

[#3]

I have also tried to use *12.

This doesn't work either.

[anil2185 · Accepted solution]

Hi,

I think below statement will solve your problem.

=Sum({$<YearMonth={"<$(=Max(CalendarMonthYear))"}>}Qty)

Don't forget to calculate your dates by below method.

year(Invoicedate) * 100 + num(Month(Invoicedate)) as YearMonth

and

year(Date)*100 + num(Month(Date)) as CalendarMonthYear

Regards,

Anil

[ashishkalia]

Gud one Anil .
IOV i hope this will help

[#6]

Hi

just a note about date format

I think the best is to store as well your date under

makedate(year(Date) , num(Month(Date)) as Calendat_01MonthYear.

this will give you 01/MM/YYYY which is easy to uwe if you need to perform some comparaison with addmonths()

best regards

Chris

[#7]

Hello Anil and Manesh, Both your answers and sample helped me out. =Sum({$}) Also, Can you explain why your need to multiply the Year with 100? Or do you have a link where I can learn this?

[ashishkalia]

Ivo Keereweer you are required to multiply the things for the simple reason
Suppose I have a date1 as 16/02/2014 (16-feb-2014)

so  year(date1)*100+month(date1)=201400+02=201402 {this is making the month year}

Hope it will help
regards
Ashish Kalia