topic last 3 Month Trending in QlikView

last 3 Month Trending

Anonymous — Thu, 23 Mar 2017 15:56:22 GMT

Hi, need to have a 3 month bar chart based on the below data and it should change automatically on change of month/year. please let me know if anyone can help me here..thnx

MonthEndDate	A	B	C
1/31/2016	1	10.3	80
2/28/2016	2	15.6	80
3/31/2016	3	20.9	80
4/30/2016	4	26.2	80
5/31/2016	5	31.5	80
6/30/2016	6	36.8	80
7/31/2016	7	42.1	80
8/31/2016	8	47.4	80
9/30/2016	9	52.7	80
10/31/2016	10	58	80
11/30/2016	11	63.3	80
12/31/2016	12	68.6	80
1/31/2017	13	73.9	80
2/28/2017	14	79.2	80
3/31/2017	15	84.5	80

Re: last 3 Month Trending

Anil_Babu_Samineni — Thu, 23 Mar 2017 16:06:01 GMT

Which metric you want to use here. This case, You want after selection. May be this?

Sum({<MonthEndDate = {">= AddMonths(Max(MonthEndDate),-3) <= Max(MonthEndDate)"}>}A+B+C)

with date format

Sum({<MonthEndDate = {">= Date(AddMonths(Max(MonthEndDate),-3),'MM/DD/YYYY') <= Date(Max(MonthEndDate),'MM/DD/YYYY')"}>}A+B+C)

Re: last 3 Month Trending

Anonymous — Thu, 23 Mar 2017 16:26:31 GMT

Thnx Anil. For some reason i am getting 0 as a result and chart is not appearing..Any idea why?

Re: last 3 Month Trending

Anil_Babu_Samineni — Thu, 23 Mar 2017 16:35:37 GMT

Can you select 3/31/2017? Then look the data

Re: last 3 Month Trending

Anil_Babu_Samineni — Thu, 23 Mar 2017 16:38:29 GMT

My Fault,

Sum({<MonthEndDate = {">= $(=Date(AddMonths(Max(MonthEndDate),-3),'MM/DD/YYYY')) <= $(=Date(Max(MonthEndDate),'MM/DD/YYYY'))"}>}A+B+C)

Re: last 3 Month Trending

Anonymous — Thu, 23 Mar 2017 16:38:36 GMT

Its still 0. thnx

Re: last 3 Month Trending

Anonymous — Thu, 23 Mar 2017 16:41:44 GMT

sry, no luck..showing 0..

Re: last 3 Month Trending

Anil_Babu_Samineni — Thu, 23 Mar 2017 16:49:27 GMT

Would you share sample qvw

Re: last 3 Month Trending

Anonymous — Thu, 23 Mar 2017 17:05:28 GMT

Sorry, due to privacy I cant..but below one worked Also tested by selecting the dates and it works fine.. Now how can I have a MM-YYYY format and select instead of date... thnx for your help

=sum({<MonthEndDate={'>=$(=MonthStart(Max(MonthEndDate),-3))<=$(=MonthStart(Max(MonthEndDate),1))'}>} A+B+C)

Re: last 3 Month Trending

Anil_Babu_Samineni — Thu, 23 Mar 2017 17:10:30 GMT

May be MonthName(MonthEndDate,'MM-YYYY') or MonthName(MonthEndDate)

Your expression seems you want to show Measure for last three months data with starting date of Month and till This month(I meant, Selection) of monthstart date. That case above condition should work. From here you can make in set analysis

=sum({<MonthEndDate={'>=$(=Date(MonthStart(Max(MonthEndDate),-3)),'YourDateFormat')<=$(=Date(MonthStart(Max(MonthEndDate),1)),'YourDateFormat')'}>} A+B+C)

Re: last 3 Month Trending

Anonymous — Thu, 23 Mar 2017 17:18:26 GMT

Thanks for your help Anil!

Re: last 3 Month Trending

Anil_Babu_Samineni — Thu, 23 Mar 2017 17:21:26 GMT

Glad i was able to help, So if your question is answered you may close this thread by flag "Correct Answer" and "Helpful"

Re: last 3 Month Trending

Anonymous — Thu, 23 Mar 2017 17:51:14 GMT

I'm new to this community...where can I close this thread? thnx

Re: last 3 Month Trending

Anil_Babu_Samineni — Thu, 23 Mar 2017 17:56:20 GMT

May be look here -- Qlik Community Tip: Marking Replies as Correct or Helpful

If you are not found, Let it be