Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Till last month data

Hi all,

I am trying to display the data till end of the last month in line chart.

but it is printing the value of this month also. Please help on it.

Dimension:

if((Month(CanonicalDate)),Date(MonthEnd(CanonicalDate), 'MM/DD/YYYY'),NULL())

Measures:

1. Count({$<DateType={'accessed'}>}invitation_id)

2. count({$<user_signed={1},DateType = {'signed'}>}user_signed)

Regards,

Pramod

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Till last month data

{"<=$(=MonthEnd(Max(CanonicalDate), -1))"}

5 Replies
MVP
MVP

Re: Till last month data

Just use Month(CanonicalDate) or CanonicalDate as the dimension, and filter the expressions like:

=Count({$<DateType = {'accessed'}, CanonicalDate = {"<=$(=MonthEnd(Max(CanonicalDate)))"}>} invitation_id)

=Count({$<user_signed = {1}, DateType = {'signed'}, CanonicalDate = {"<=$(=MonthEnd(Max(CanonicalDate)))">} user_signed)

Not applicable

Re: Till last month data

Your expression is taking max of canonicaldate means present month.

I need till last month.

For ex:

Max(CanonicalDate) is 11-5-2015,  Then it will take present month end


How can i restrict to last month

MVP
MVP

Re: Till last month data

{"<=$(=MonthEnd(Max(CanonicalDate), -1))"}

Not applicable

Re: Till last month data

Is this correct for count of two months

if(mod(Date(monthStart(CanonicalDate), 'MM/DD/YYYY'),2)=0,Date(monthStart(CanonicalDate), 'MM/DD/YYYY'),NULL())

Re: Till last month data

try

Count({$<DateType = {'accessed'}, CanonicalDate = {"<=$(=MonthEnd(addmonths(Max(CanonicalDate),-1)))"}>} invitation_id)