Need Help on alternate states with set analysis....?

[vishalgoud]

Hi Experts ,

we have a requirement where we use alternates states in set analysis , getting some error in the Expression

if( GetSelectedCount(ENV ,'[Local 1]') =1  and GetSelectedCount(YEAR ,'[Local 1]')= 1  and GetSelectedCount(MONTH ,'[Local 1]') = 1 ,

Sum( {'[Local 1]' < REPORT_ID  = {"1"} , MONTH = $:: {">=$(= addmonths(Max(MONTH ),-2)) <=$(= addmonths(Max(MONTH),0))"}, SOURCE_ID =$::{"1"}, TARGET_ID =$::{"2"}

,DATE = $:: DATE >} SOURCE_COUNT) ) , Sum([Local 1]{< REPORT_ID = $:: {"1"} , SOURCE_ID = $:: {"1"}, TARGET_ID = $:: {"2"} , DATE = $:: DATE >} SOURCE_COUNT) .

we are using Month as a Dimention &  our alternate group name is   Local 1 , and we are getting the error in the Expression. shown in Red.

Please have a Look into the Expression and Correct us.

Thanks in advance.

Best Regards,

V.

[sunny_talwar]

I sure can.

What is the confusion. May be I can help you without you needing to open the document

[vishalgoud]

Hi sunny ,

In your shared Qvw , for the first time when you select global filters like year and month we are getting only one month data.

But Our requirement is when we select any one value from each of the global filters we have to get the last 3 months data including the selected month. and if we select 2 values from any one filter then have to show the data as per the selections no need of last 3 months.

and after selecting the global filters , if we select section 1 filters then it has to override the global filters and same again i.e  if one value from each of the section wise filters selected then it has to show last 3  months data otherwise normal as per the selection...

and we want the same kind of response when we from global to section and section to global filters.

Seems like we are taking your valuable time , but we are helpless on this issue..

can you please try and shared the final Doc.

Best Regards,

V.

[sunny_talwar · Accepted solution]

So this is what I have for you.

Expression for State 1:

=If(GetSelectedCount(Year, False(), 'state1') = 1,

  If(GetSelectedCount(MONTH, False(), 'state1') = 1,

  Sum({state1<DATE ={">=$(=TimeStamp(MonthStart(Max({state1}DATE),-2), 'M/D/YYYY h:mm:ss TT')) <=$(=TimeStamp(MonthEnd(Max({state1}DATE),0), 'M/D/YYYY h:mm:ss TT'))"}, Year, MONTH>} SUCCESS_RATE),

  Sum({state1} SUCCESS_RATE)),

If(GetSelectedCount(Year) = 1,

  If(GetSelectedCount(MONTH) = 1,

  Sum({<DATE ={">=$(=TimeStamp(MonthStart(Max(DATE),-2), 'M/D/YYYY h:mm:ss TT')) <=$(=TimeStamp(MonthEnd(Max(DATE),0), 'M/D/YYYY h:mm:ss TT'))"}, Year, MONTH>} SUCCESS_RATE),

  Sum(SUCCESS_RATE))))

Calculation Condition on General tab for State1 Chart:

GetSelectedCount(MONTH) > 0 and  GetSelectedCount(Year) = 1

Expression for State2:

=If(GetSelectedCount(Year, False(), 'state2') = 1,

  If(GetSelectedCount(MONTH, False(), 'state2') = 1,

  Sum({state2<DATE ={">=$(=TimeStamp(MonthStart(Max({state2}DATE),-2), 'M/D/YYYY h:mm:ss TT')) <=$(=TimeStamp(MonthEnd(Max({state2}DATE),0), 'M/D/YYYY h:mm:ss TT'))"}, Year, MONTH>} SUCCESS_RATE),

  Sum({state2} SUCCESS_RATE)),

If(GetSelectedCount(Year) = 1,

  If(GetSelectedCount(MONTH) = 1,

  Sum({<DATE ={">=$(=TimeStamp(MonthStart(Max(DATE),-2), 'M/D/YYYY h:mm:ss TT')) <=$(=TimeStamp(MonthEnd(Max(DATE),0), 'M/D/YYYY h:mm:ss TT'))"}, Year, MONTH>} SUCCESS_RATE),

  Sum(SUCCESS_RATE))))

Calculation Condition on General tab for State2 Chart:

GetSelectedCount(MONTH) > 0 and  GetSelectedCount(Year) = 1

Demonstration for state 1 chart:

1) Selection in global filter

2) 2 month selection in global filter

3) Selection made in state1 filters (overriding the global filters)

4) 2 month selection in state2 filter

[vishalgoud]

Thanks a lot Sunny T,

its working fine now , can you please  rewrite the Expression by including the Environment ID in the Filters,

I mean we have 4 filters totally , EnvID , Year , Month and date..but in the sample I shared I have given only 2 filters.

1. when we select any one value from each drop down then only the chart has to display. so finally  to display the chart every drop down have some selection.

2. and I have to mention source ID = 500  and Target ID = 102  in the set expression.

can you please include the above the send the Expression.

Best Regards,

V.

[sunny_talwar]

Vishal have you tried doing that at your end? I don't mind helping you, but there are two problems here:

1) If I spoon feed you everything how are you going to learn? Give it some try and see if you get it to work.

2) Not having the application in front of me might make it difficult to get it precisely as you want. May be if you do it on your own, you can get it right the very first time.

Don't get me wrong my friend, I am more than willing to help you out, but my suggestion to you is, give it a shot 3-4 times and see if you can get it work on your own end. If then it doesn't work, I will be more than happy to help you.

Best,

Sunny

[vishalgoud]

Hi Sunny ,

We implemented your thought in our application , thanks for the help and encouragement ,  we are able to over ride the selections of global filters with section filters but we are unable to do in reverse .i.e after selecting  the filters at  section level , when we select any value from global filter we are unable to clear the section wise filters...

as you know we have to clear all the section level filters when some value is selected from global filters.

Any work around for that..

Best Regards,

V

[sunny_talwar]

Are you looking for a way so that if after making selections in local filters, the user change their selections in global filter, the application will start changing based on the global filters ignoring local filters? AFAIK this is not something we can do in QlikView and I would think that to be very confusing for the users.

[vishalgoud]

Hi Sunny ,

Your Correct , I conveyed the same in the Discussion and come up with a new Way to show this.

Please look into the Below image.

is it possible to show in the above method. its all about comparing reports in different Env, year , Month and Dates...

here the global and report 1 filters are in sink (default state ), and the report 2 , report 3 filters are in alternate states.

for the first time when the user selected above selections in global filters then we have to show its previous months in Report2 and Previous to  previous in report 3.after that is it possible to customize the selections for report 2 and Report 3 by keeping the Report 1 in same selection.

Please Respond with work around if it is possible. we are unable to include 2 states in one Exp.

adding the sample Qvw in the main thread (sample for alternate states) , please look into it.

Best Regards,

V.

[sunny_talwar]

See if this meets your requirement

When selections have been made in all three states:

When its only made in inherited state:

When its made in Group 1 and Inherited:

So on and so forth....

Expression Used:

RangeSum(

Sum({<DATE2 ={">=$(=TimeStamp(MonthStart(Max(DATE2), 0),'M/D/YYYY h:mm:ss TT'))<=$(=TimeStamp(MonthEnd(Max(DATE2), 0),'M/D/YYYY h:mm:ss TT'))"},YEAR,MONTH >} [Source count]),

If(Len(Trim(GetCurrentSelections(Chr(10), ': ', ',', 10000, 'Group 1'))) > 0,

Sum({[Group 1]<DATE2 ={">=$(=TimeStamp(MonthStart(Max({[Group 1]}DATE2), 0),'M/D/YYYY h:mm:ss TT'))<=$(=TimeStamp(MonthEnd(Max({[Group 1]}DATE2), 0),'M/D/YYYY h:mm:ss TT'))"},YEAR,MONTH >} [Source count]),

Sum({<DATE2 ={">=$(=TimeStamp(MonthStart(Max(DATE2),-1),'M/D/YYYY h:mm:ss TT'))<=$(=TimeStamp(MonthEnd(Max(DATE2),-1),'M/D/YYYY h:mm:ss TT'))"},YEAR,MONTH >} [Source count])),

If(Len(Trim(GetCurrentSelections(Chr(10), ': ', ',', 10000, 'Group 2'))) > 0,

Sum({[Group 2]<DATE2 ={">=$(=TimeStamp(MonthStart(Max({[Group 2]}DATE2), 0),'M/D/YYYY h:mm:ss TT'))<=$(=TimeStamp(MonthEnd(Max({[Group 2]}DATE2), 0),'M/D/YYYY h:mm:ss TT'))"},YEAR,MONTH >} [Source count]),

Sum({<DATE2 ={">=$(=TimeStamp(MonthStart(Max(DATE2),-2),'M/D/YYYY h:mm:ss TT'))<=$(=TimeStamp(MonthEnd(Max(DATE2),-2),'M/D/YYYY h:mm:ss TT'))"},YEAR,MONTH >} [Source count])))

[vishalgoud]

Hi sunny,

Thanks a lot you are awesome man, I want to learn from you, Please let me know your blogs and books If you have any...

want to follow you please send your twitter name.