Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead 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
anil2185
Contributor III
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

View solution in original post

7 Replies
maneshkhottcpl
Partner - Creator III
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.

anil2185
Contributor III
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

ashishkalia
Partner - Creator
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?

ashishkalia
Partner - Creator
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