topic Re: Displaying last 12 months trend in QlikView

Displaying last 12 months trend

Thu, 26 Dec 2013 05:12:01 GMT

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.

Re: Displaying last 12 months trend

Anonymous — Thu, 26 Dec 2013 05:35:42 GMT

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

Re: Displaying last 12 months trend

Thu, 26 Dec 2013 05:39:54 GMT

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')

Re: Re: Displaying last 12 months trend

Thu, 26 Dec 2013 10:03:21 GMT

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

Re: Displaying last 12 months trend

Anonymous — Thu, 26 Dec 2013 10:07:22 GMT

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

Re: Displaying last 12 months trend

Thu, 26 Dec 2013 10:11:29 GMT

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

Re: Displaying last 12 months trend

Thu, 26 Dec 2013 10:18:31 GMT

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

Re: Displaying last 12 months trend

suresh_rawat — Thu, 26 Dec 2013 10:19:33 GMT

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

Re: Re: Re: Displaying last 12 months trend

Thu, 26 Dec 2013 10:19:43 GMT

Hi Anushree

PFA.... Hope this will help you

Re: Displaying last 12 months trend

Anonymous — Thu, 09 Oct 2014 10:32:39 GMT

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

Re: Displaying last 12 months trend

Anonymous — Thu, 09 Oct 2014 10:39:28 GMT

Hi suresh,

i need last 12 months remaining due based on TRXDATE can you please write the syntax

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

if i select vtrxdate as mar2014 ,it has to show from april 2013 to mar 2014

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

Thanks,

krishna

Re: Displaying last 12 months trend

jyothish8807 — Thu, 09 Oct 2014 10:46:33 GMT

Hi Anushree,

Try this:

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

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

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

Regards

Re: Displaying last 12 months trend

Anonymous — Thu, 09 Oct 2014 11:18:28 GMT

Hi jyothish,

i need last 12 months remaining due based on TRXDATE can you please write the syntax

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

if i select vtrxdate as mar2014 ,it has to show from april 2013 to mar 2014

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

i tried asper you said but i am facing some syntax issue can u please help me with syntax

Thanks,

krishna