Help in SET analysis ... Trailing 6 months

[sandeepprasad_j]

Hello Experts,

I am attaching my qvw application ...

Can you please help me with the 6 months trailing period please ...

Its very urgent ...

Thanks a heap in advance.

Sandeep.

[datanibbler]

Hi sandeepprasad,

I dont quite understand - you want the six months before the one that's selected (or, the six before the one on your dimension), right?

There was a thread about a very similar problem just now, look here http://community.qlik.com/thread/99575

HTH

Best regards,

DataNibbler

[sandeepprasad_j]

Hello Nibbler,

When a selecion is made in the [Year-Month] for a particular [Year-Month] lets say 2013-OCT , from 2013-OCT the graph has to show previous 6 months data including the selected [Year- Month].

The below is the expression i am using

=sum({$<Year=,[Year - Month]=,Monthstart=,date_con_Monthly ={'>=$(=MonthStart(addmonths(max(date_con_Monthly),-5)))<=$(=MonthEnd(max(date_con_Monthly)))'},[Metric_ID] ={'MN_PR_002'} >} Result).

But it is not working ...

Can i get help regarding the same.

Thanks in advance .

Sandeep.

[datanibbler]

Hi sandeepprasad,

you don't need a SET_expression as complicated as that. Using >>> num(month(today())) <<<, for example, you get a number - 11 for November - that makes calculations much easier.

=> If you have a master_calendar for use in your app, then just introduce a field "Month_num" into that.

Then you can write a Set_expression just specifying

- "Current year"

- "Month_num >= (current month) - 6"

HTH

Best regards,

DataNibbler

[sandeepprasad_j]

Hello Nibbler,

Thanks for the inputs ,but i am using same expression for more that 24 sheets and some 80 charts ...

Is there any way that i can use my expresion by doing some modifications..

Please can u help on this .

Thanks,

Sandeep.

[#6]

Hi,

     I checked your expression. but when I opened table viewer then there was no value in "date_con_Monthly" field which is in Calander table. May be all records are showing there beacuse of thousands of records. but if there is no value in  "date_con_Monthly" field then how are you checking your records with that field in expression. I also comment only that part of your expression that time, like this:

=sum({$<Year=,

  [Year - Month]=,

  Monthstart=,

//date_con_Monthly ={'>=$(=MonthStart(addmonths(max(date_con_Monthly),-5)))  <=$(=MonthEnd(max(date_con_Monthly)))'},

  [UKMetric_ID_Monthly] ={'UK_MN_PR_002'} >} UKResult_Monthly)/1000

This expression  gave same result as previous.

If you understand the issue then please let me know one thing that  with which Date field you want to check your expression condition,  "date_con_Monthly" or any other date field ??

Thanks,

Ashutosh

[neha_shirsath]

Hi,

Try the below expression-

=Sum({< [Event Date]={">$(=Monthstart(Max([Event Date]),-5))<=$(=MonthEnd(Max([Event Date])))"},

Year=, [Year - Month]= >} UKResult_Monthly)/1000

-Neha

[sandeepprasad_j]

Hello Sharma,

Thanks a ton for your help...

The way you have explained me ,really helped..

Sandeep.

[sandeepprasad_j]

Hello Neha,

Thanks Very much for your help ....

Sandeep.