topic Re: Show past 12 months in QlikView

Show past 12 months

Fri, 09 Nov 2012 15:33:33 GMT

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

MayilVahanan — Fri, 09 Nov 2012 15:54:06 GMT

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

vVar1

=AddMonths(Date,-12)

vVar

=AddMonths(Date,0)

Re: Show past 12 months

CELAMBARASAN — Fri, 09 Nov 2012 15:55:16 GMT

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

Fri, 09 Nov 2012 16:03:49 GMT

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

Fri, 09 Nov 2012 16:05:23 GMT

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

CELAMBARASAN — Fri, 09 Nov 2012 16:08:32 GMT

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

Re: Show past 12 months

Fri, 09 Nov 2012 16:10:54 GMT

Yes, they are the same

Re: Show past 12 months

CELAMBARASAN — Fri, 09 Nov 2012 16:14:33 GMT

Try this

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

There is a typo mistake in braces

Re: Show past 12 months

Fri, 09 Nov 2012 16:21:03 GMT

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

CELAMBARASAN — Fri, 09 Nov 2012 16:36:57 GMT

It will work. can you send me a sample?

Re: Show past 12 months

Fri, 09 Nov 2012 16:45:39 GMT

Sure...attached is the QVW

Re: Show past 12 months

MayilVahanan — Fri, 09 Nov 2012 16:49:54 GMT

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

Hope it helps

Re: Show past 12 months

Fri, 09 Nov 2012 16:54:35 GMT

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

I appreciate the assistance

Re: Show past 12 months

CELAMBARASAN — Fri, 09 Nov 2012 16:57:20 GMT

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.

Re: Show past 12 months

Anonymous — Thu, 09 Oct 2014 10:43:06 GMT

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.

=rangesum(above(Sum(If(TRXDATE>addmonths(vTrxDate,-12) and DATE>vTrxDate and REMAININGDUE)),0,RowNo()))/100000

can u please any one help me its urgent requirement

Thanks,

krishna