14 Replies Latest reply: Oct 9, 2014 7:12 AM by Siva k

# Show past 12 months

I need to show data for the past 12 months of the current date (including current month)

Currently, my dimension for my chart is a calculated dimension:

=date(MonthName([rcptdate]), 'MM-YY')

How do I only show the past 12 months?

• ###### Re: Show past 12 months

Hi

=sum({<Date = {'>=\$(=vVar1)<=\$(=vVar)'}>}Sales)

vVar1

vVar

• ###### Re: Show past 12 months

This can be done using set analysis

Example if ur date field is rcptdate and ur present expression is Sum(Sales)

Sum({<rcptdate={">=\$(=MonthStart(today(),-12)))<=\$(=Today())"}>} Sales)

If your requirement is based on the given date

Sum({<rcptdate={">=\$(=MonthStart(vSelectedDate,-12)))<=\$(=vSelectedDate)"}>} Sales)

vSelected date is a variable which has the date selected.

• ###### Re: Show past 12 months

Celam:

Where exactly do I put this?    If I put it in expression, it tells me "No data to display"

and my current expression was:

Sum(extcost)

Then, changing it using  your tip:

Sum({<rcptdate={">=\$(=MonthStart(today(),-12)))<=\$(=Today())"}>} extcost)

And I get "no data can be displayed"

Any ideas?

• ###### Re: Show past 12 months

Actually, I think I found the first issue:

I change the Today()  to Now()  and that seemed to get data to come back

It is, however, still giving me all data back, and not the past 12 months

• ###### Re: Show past 12 months

Check the date format for both MonthStart(Today()) and your rcptdate field. Is it same?

• ###### Re: Show past 12 months

Yes, they are the same

• ###### Re: Show past 12 months

Try this

Sum({<rcptdate={">=\$(=MonthStart(today(),-12))<=\$(=Today())"}>} extcost)

There is a typo mistake in braces

• ###### Re: Show past 12 months

That gives me "no data to display".    If I change Today() to Now() I get data, but all time data, not past 12 months

• ###### Re: Show past 12 months

It will work. can you send me a sample?

• ###### Re: Show past 12 months

Sure...attached is the QVW

• ###### Re: Show past 12 months

HI

Sum({<rcptdate={">=\$(=timestamp(MonthStart(Today(),-12)))<=\$(=timestamp(Today()))"}>}extcost)

Hope it helps

• ###### Re: Show past 12 months

That worked!  Thank you so much...still getting the hang of QlikView

I appreciate the assistance

• ###### Re: Show past 12 months

HI Ram,

i need last 12 months remaining due  for trx date can you please write the syntax

vtrx date is nothing but today date i need to to get last 12 months remaining due can u please write the syntax.

can u please any one help me its urgent requirement

Thanks,

krishna

• ###### Re: Show past 12 months

Hi,

Please make sure that set analysis expression value and field are same format.

MonthStart returns only date but your date field has both date and time means timestamp format.

This should be considered on using date comparison in set analysis.