topic Re: Dynamic Trend Display in QlikView

Dynamic Trend Display

Fri, 27 Dec 2013 04:23:46 GMT

Hi,

I am working on a requirement wherein I need to show a chart that displays values of the past 12 months including the current selection.i.e., for eg if i have selected 2013 September the chart must display values for month starting from 2013 Sep to 2012 Aug in the same order and incase i select 2012 Jul the chart must display values for month starting from 2012 Jul to 2011 Jun in the respective order and suppose if no selection is made it must consider the current year and current month-1 (i.e., 2013 Nov) and display values for 2013 Nov to 2012 Oct. Can anyone Please let me know how this could be accomplished.I have attached a sample document for reference

Re: Dynamic Trend Display

tresB — Fri, 27 Dec 2013 04:50:29 GMT

Try expression like:

=sum({<My_Year=, My_Month=,My_date={">$(=Date(Addmonths(Max(My_date),-12),'M/D/YYYY')) <=$(=Date(Max(My_date),'M/D/YYYY'))"}>} Value)

PFA

Re: Dynamic Trend Display

suresh_rawat — Fri, 27 Dec 2013 05:08:01 GMT

Hi Anushree,

This is working fine on my application, hope it will work out for you.

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

Regards

Suresh Rawat

Re: Dynamic Trend Display

Fri, 27 Dec 2013 05:44:29 GMT

Anushree,

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

i think my attachment is the correct solution you are looking for

Regards

Nuthan

Re: Dynamic Trend Display

Fri, 27 Dec 2013 06:33:26 GMT

HI Tresesco,

The application that you have put up works as per the requirement but it is not sorted accordingly.Can you please let me know what could be done so that the data is also sorted ie., I would want to see the data in the descending order .

For eg if I have selected Mar 2012 I would want to see the data starting from Mar 2012,Feb 2012,Jan 2012,Dec 2011... to Apr 2011

Re: Dynamic Trend Display

Fri, 27 Dec 2013 06:38:33 GMT

Nuthan,

I see that the application put up by you is not reflecting the correct data.

For eg when I select 2013 Mar the value displayed for 2012 Apr in the chart is not correct.Can you please look into it

Re: Re: Dynamic Trend Display

tresB — Fri, 27 Dec 2013 06:56:01 GMT

Hi,

Sort expression:

=Only({<My_Year=, My_Month=,My_date={">$(=Date(Addmonths(Max(My_date),-12),'M/D/YYYY')) <=$(=Date(Max(My_date),'M/D/YYYY'))"}>} My_Year*100+Num(My_Month)) // Descending for the second dimension

PFA

Note: Things could have been easier had you have a field calculated in the script as: MonthName( Date) as MonthName.

Re: Dynamic Trend Display

Fri, 27 Dec 2013 07:25:19 GMT

Can you Please let me know how I could use MonthName and ease up the application.

Thanks & Regards,

Anushree Shetty

Re: Re: Dynamic Trend Display

tresB — Fri, 27 Dec 2013 07:36:10 GMT

Create a new field like:

Load

Date,

MonthName(Date) as MonthName

from<>;

In the report take this MonthName as second dimension.

Re: Dynamic Trend Display

Fri, 27 Dec 2013 08:35:40 GMT

Anushree,

Check out this attachment

Re: Dynamic Trend Display

Fri, 27 Dec 2013 10:06:31 GMT

HI Tresesco,

I did try using the expression you have suggested in my original application But I find that it is not returning any value when the comparison operators like < and > are used within the set expressions

The expression that i have been using in my original application is this-

sum({<CommonYear=, CommonMonth=,[Common Date]={">$(=Date(Addmonths(Max([Common Date]),-12),'DD/MM/YYYY'))<=$(=Date(Max([Common Date]),'DD/MM/YYYY'))"}>}MetricValue)

the above expression works fine when the filter for common date is split and '=' is used instead of '<' and' >' as

sum

({<CommonYear=, CommonMonth=,[Common Date]={"=$(=Date(Max([Common Date]),'DD/MM/YYYY'))"}>}MetricValue)

sum({<CommonYear=, CommonMonth=,[Common Date]={">$(=Date(Addmonths(Max([Common Date]),-12),'DD/MM/YYYY'))"}>}MetricValue)

So the Problem is not the date format.Can you please let me know where I am going wrong in the first expression as I am not getting any data

Re: Dynamic Trend Display

tresB — Fri, 27 Dec 2013 10:27:29 GMT

Still I guess, it would be format issue. Comparison is not working properly. Please post your app with sample data.

Re: Re: Dynamic Trend Display

Mon, 30 Dec 2013 05:41:00 GMT

Hi Tresesco,

I tried changing the format and the expression is working fine as you suggested.But I see that in my application when i select Year 2013 and Month Mar data is displayed only for Mar 2013 and the data for the previous 12 months are not getting displayed.So can you Please look into this issue and let me know where I have gone wrong in getting the desired output.

I could not understand why you have multiplied year with 100 in the below sort expression.

=Only({<My_Year=, My_Month=,My_date={">$(=Date(Addmonths(Max(My_date),-12),'M/D/YYYY')) <=$(=Date(Max(My_date),'M/D/YYYY'))"}>} My_Year*100+Num(My_Month))

So can you Please explain the same as well

I have attached the sample document for reference

Re: Dynamic Trend Display

tresB — Mon, 30 Dec 2013 05:58:57 GMT

Exclude IF from the expression and try:

=sum({<Flag1={'-1'},Category={'Program Efficiency'},CommonYear=,CommonMonth=,[Common Date]={">$(=Date(Addmonths(Max([Common Date]),-12),'M/D/YYYY')) <=$(=Date(Max([Common Date]),'M/D/YYYY'))"}>}Metric_Value)

Re: Re: Dynamic Trend Display

Mon, 30 Dec 2013 06:48:06 GMT

Thank you so much Tresco.Its working as intended .Can you please explain the logic that you have used for sorting.I could not understand why you are multiplying year with 100?

Incase i want to see the data in ascending order what should be the logic?