Displaying last 12 months trend

[#1]

Hi,

I am new to qlikview i am stuck up with a requirement wherein i must show the data for last 12 months in a chart for which i have been using an expression -

only({<CommonMonth={"$(=month(AddMonths(date(today()),-1)))"}>}MetricValue) ,only({<CommonMonth={"$(=month(AddMonths(date(today()),-2)))"}>}MetricValue)......only({<CommonMonth={"$(=month(AddMonths(date(today()),-12)))"}>}MetricValue).

But the issue is suppose if the current date(i.e., today's date ) is 01/09/2013(DD/MM/YYYY)current month-9 must point to 01/12/2012 but currently it refers to 01/12/2013.Thus the result is not appreciable.So can anyone let me know how I could get the trend display based on the current month dynamicaly without any hardcoding.

[Anonymous]

why not you use date fields instead of using month or use Month Year...

only({<CommonDate={">=$(=AddMonths(date(today()),-12)) <=$(=date(today()))"}>}MetricValue)

You should be careful while using date fields as, date format of Common date and dates calculated by $ expansions should be same, or either you can create numeric values of all dates and then compare>>>

"Create num(CommonDate) as CommonDateNUM"  in script only and then use the following expression.

only({<CommonDateNUM={">=$(=num(AddMonths(date(today()),-12))) <=num($(=date(today())))"}>}MetricValue)

Hope it helps

Regards

Nitin

[#3]

Hi Anushree

Try this

=sum({<yourDateField={'>=$(Vrolling12)<=$(vMaxDate)'}>}Sales)

Variables:

Vrolling12=Date(MonthStart(vMaxDate,-11),'DD/MM/YYYY')

vMaxDate=Date(max(YourDatefield),'DD/MM/YYYY')

[#4]

Hi,

I tried the approach suggested but when i make a selection on the year, the application does not show any results.Could you Please look into it and let me know how I could ensure the display trend when the year is selected.

For Eg considering the current month and year which is December 2013 I would want to show the data from Nov 2013 to Jan 2012.

I have attached the sample document

[Anonymous]

use sum instead of only............

[#6]

In the attachment that I have put up, sum function is been used instead of only .But still the values are not getting displayed

Thanks & Regards,

Anushree Shetty

[#7]

Hi Nirmal,

I tried the approach that you have suggested.But,then the values are not getting displayed.Could you please let me know if there is any other turn around. I have put up a sample attachment in my previous reply Please let me know how i could acheive the requirement

[suresh_rawat]

Hi Anushree,

Use this expression, it works perfect in my application.

sum({1<Date = {">=$(=MonthStart(Max(Date), -11))<=$(=MonthEnd(Max(Date)))"}>}Data)

Hope this will work out for you.

Regards

Suresh Rawat

[#9]

Hi Anushree

PFA.... Hope this will help you

[Anonymous]

HI Nirmal,

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

Thanks,

krishna