Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for
Did you mean:
Not applicable

## Compare Yearmonth with selected yearmonth

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?

1 Solution

Accepted Solutions
Contributor III

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

7 Replies
Partner - Creator III

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

Not applicable
Author

I have also tried to use *12.

This doesn't work either.

Contributor III

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

Partner - Creator

Gud one Anil .
IOV i hope this will help

Not applicable
Author

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

Not applicable
Author

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?

Partner - Creator

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

Community Browser