Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

nickedw0
New Contributor III

Expression to select prior period data given month, year

I have a calendar with fields for Month, Year, MonthYear. Formatted as follows:

Month  Year  MonthYear

Jul          2017     Jul-2017

Jun         2017     Jun-2017

If selection for Month is Jul, Year is 2017, How do I count records for Jun-2017?

I have the following expression, but it returns nothing:

Count({$<MonthYear={"$(=Date(AddMonths(GetFieldSelections([Month],1) &'-' & GetFieldSelections([Year],1),-1),'MM-YYYY')"}>} InvoiceNo)

Any help appreciated, Thanks

1 Solution

Accepted Solutions
vinieme12
Esteemed Contributor II

Re: Expression to select prior period data given month, year

4 Replies
Highlighted
MVP
MVP

Re: Expression to select prior period data given month, year

How do you create MonthYear field in the script? Like this

Date(MonthStart(Date), 'MMM-YYYY') as MonthYear?

If you do, then you can try this

Count({$<MonthYear={"$(=Date(MonthStart(Max([Month], -1),'MM-YYYY'))"}, Year, Month>} InvoiceNo)

vinieme12
Esteemed Contributor II

Re: Expression to select prior period data given month, year

YoussefBelloum
Esteemed Contributor

Re: Expression to select prior period data given month, year

Hi,

Count({$<MonthYear={"$(=Date(AddMonths(GetFieldSelections([Month],1) &'-' & GetFieldSelections([Year],1),-1),'MM-YYYY')"}>} InvoiceNo)


it is returning nothing because here (for the first part) you compare a calculated field with a date, the format is different.. Also the input for the Addmonths function is also wrong





nickedw0
New Contributor III

Re: Expression to select prior period data given month, year

The following worked for me. Thanks everyone for your suggestions and guidance. I used guidance from ' Dates in Set Analysis". Since I was pulled data other than associated data, the $ had to be a 1.

Count( {1<MonthYear={"$(=Date(AddMonths(Max(MonthYear),-1),'YYYY-MMM'))"}>} LoanID )