Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Help to get last three months data in qlikview

i want to display last two months data in pivot table 

I am doing this using the following expression in qlikview but that doesn't work for me. Please help. 

Count({<Month={">=$(=Month,-3)) <=$(=Month)"}>}Item)

OR

Count({<Month={">=$('01-05-2020')<=$('30-06-2020')"}>}Item)

 

 

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Specialist III
Specialist III

Hi, what is the format of your month field?

I think you should create in your preceding load statement a YearMonth field with 

 

load Date#(YearField&MonthField, 'YYYYMM') as YearMonth

 

And  use in your SA something like 

 

Count({<YearMonth={">=$(=AddMonths(Max(YearMonth), -3))"}>} Item)

 

View solution in original post

8 Replies
Highlighted
Specialist III
Specialist III

Hi, what is the format of your month field?

I think you should create in your preceding load statement a YearMonth field with 

 

load Date#(YearField&MonthField, 'YYYYMM') as YearMonth

 

And  use in your SA something like 

 

Count({<YearMonth={">=$(=AddMonths(Max(YearMonth), -3))"}>} Item)

 

View solution in original post

Highlighted
Contributor
Contributor

I have already stored Month and Year into Month Field and change into Date format 'MM-YYYY' i.e. 07-2020. But the count expression is not working. It is not displaying any data.

Load

Date(MonthStart(OrderDate),'MM-YYYY') as Month

 

Highlighted
Specialist III
Specialist III

And if you try with

load
Date#(Year(OrderDate) & Month(OrderDate), 'YYYYMM') as Month

 

Highlighted
Contributor
Contributor

Still not working. Please help me.

Highlighted
Contributor
Contributor

Please check what's wrong.

Highlighted
MVP
MVP

With Month as dimension you an use below expression

=sum({<Month>}aggr(rangesum(above(total count({<Month>}Item),0,3)), (Month,(NUMERIC, ASCENDING))))*avg(1)

Highlighted
Contributor
Contributor

Kush your expression has error. Kindly give me correct one

Highlighted
MVP
MVP

Expression seems fine. Just change the field name highlighted  with your actual field names.

=sum({<Month>}aggr(rangesum(above(total count({<Month>}Item),0,3)), (Month,(NUMERIC, ASCENDING))))*avg(1)