topic Re: Help in SET analysis ... Trailing 6 months in QlikView

Help in SET analysis ... Trailing 6 months

sandeepprasad_j — Fri, 22 Nov 2013 09:44:29 GMT

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.

Re: Help in SET analysis ... Trailing 6 months

datanibbler — Fri, 22 Nov 2013 09:53:17 GMT

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

Re: Help in SET analysis ... Trailing 6 months

sandeepprasad_j — Fri, 22 Nov 2013 10:03:01 GMT

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.

Re: Help in SET analysis ... Trailing 6 months

datanibbler — Fri, 22 Nov 2013 10:12:54 GMT

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

Re: Help in SET analysis ... Trailing 6 months

sandeepprasad_j — Fri, 22 Nov 2013 10:45:28 GMT

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.

Re: Help in SET analysis ... Trailing 6 months

Fri, 22 Nov 2013 11:09:16 GMT

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

Re: Help in SET analysis ... Trailing 6 months

neha_shirsath — Fri, 22 Nov 2013 11:50:43 GMT

Hi,

Try the below expression-

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

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

-Neha

Re: Help in SET analysis ... Trailing 6 months

sandeepprasad_j — Fri, 22 Nov 2013 11:51:31 GMT

Hello Sharma,

Thanks a ton for your help...

The way you have explained me ,really helped..

Sandeep.

Re: Help in SET analysis ... Trailing 6 months

sandeepprasad_j — Fri, 22 Nov 2013 11:52:31 GMT

Hello Neha,

Thanks Very much for your help ....

Sandeep.