topic Re: Need Help on alternate states with set analysis....? in QlikView

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

vishalgoud — Wed, 25 Nov 2020 16:16:04 GMT

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,

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

sunny_talwar — Fri, 04 Mar 2016 13:40:56 GMT

Can you try this:

If(GetSelectedCount(ENV, False(), 'Local 1') = 1 and GetSelectedCount(YEAR, False(), 'Local 1') = 1 and GetSelectedCount(MONTH, False(), '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))

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

vishalgoud — Mon, 07 Mar 2016 15:18:26 GMT

hi Sunny T ,

Thanks a lot for your response , your expression also not working...

am really confused with this...

is this possible to use the Alternate states in Expression which are having GetSelected functions if possible please let us know that syntax....

am unable to get it if I use the above two expressions...

Please Leave your comments and any other methods to correct this..

Thanks in advance...

Best Regards,

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

sunny_talwar — Mon, 07 Mar 2016 19:50:22 GMT

What version of QV are you using? Is it a version before QV11.20 SR8? If it is not, would you be able to share a sample with your expected output?

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

vishalgoud — Wed, 09 Mar 2016 05:50:03 GMT

No Sunny we are using Qv 11.2 SR 10 only , i think this is possible in SR 10 .

But am unable to get the output with the above expression...

we have some other appraoch to satisfy the user requirement but seems like that is also not working.

Please respond if you have any idea about that.

Please find the attached image in the main thread for clear underastanding.

its about restricting the filter selections to particular section only.

simply we have 4 sections , in each section we have Env ,Year ,Month and Report ID filters.

the same 4 filters we have to Use as a Global Filters means if we select any one of that it has to effect the Entire Report Overriding the Section wise selections...

Thanks in advance...

Best Regards,

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

Digvijay_Singh — Wed, 09 Mar 2016 06:05:30 GMT

I remember the similar requirement was posted earlier by you, I was trying on attached sample but couldn't find the usable solution. Just attaching the half done sample in case others can help you. sunindia

You can confirm if this sample can be used by others.

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

sunny_talwar — Wed, 09 Mar 2016 10:05:27 GMT

Yet again I don't have a sample to work with, but can you try this:

If(GetSelectedCount(ENV, False(), 'Local 1') = 1 and GetSelectedCount(YEAR, False(), 'Local 1') = 1 and GetSelectedCount(MONTH, False(), 'Local 1') = 1,

Sum({[Local 1]<REPORT_ID = {1}, MONTH = {">=$(=AddMonths(Max({$} MONTH), -2))<=$(=AddMonths(Max({1}MONTH), 0))"}, SOURCE_ID = {1}, TARGET_ID = {2}, DATE>} SOURCE_COUNT),

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

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

vishalgoud — Wed, 09 Mar 2016 12:13:31 GMT

Hi Sunny ,

Thanks a lot for your response , Please find the Sample Qvw application.

Almost Exact requirement I have , In the Qvw I have clearly mentioned the Global filters , section wise filters

tried in the following way : 1. global filters are in Default state , 2. section wise filters are in that particular state.

3. and all the charts in default state.

Please leave your comments here only , I don't have a Named CAL to open the Qvw.

Correct our Expression.

requirement is : for global filters the Entire report have to filter , where as for section wise filters only that particular section have to be filter.

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

sunny_talwar — Wed, 09 Mar 2016 13:15:08 GMT

Are you trying to override the global filters when you select local filters? If yes, then try these expressions:

=If(GetSelectedCount(MONTH, False(),'state1') = 1 and GetSelectedCount(Year, 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({<MONTH = $::MONTH , Year = $::Year >} SUCCESS_RATE))

=If(GetSelectedCount(MONTH, False(),'state2') = 1 and GetSelectedCount(Year, 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({<MONTH = $::MONTH , Year = $::Year >} SUCCESS_RATE))

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

vishalgoud — Wed, 09 Mar 2016 18:17:39 GMT

Once again Thank you very much sunny,

yes now we are able to override the selections with small confusion , can you please share that sample that you worked on.

I will Open it by using personal edition , I think in PE we will get 4 chances to open other applications.

Best Regards,

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

sunny_talwar — Wed, 09 Mar 2016 18:55:08 GMT

I sure can.

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

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

vishalgoud — Thu, 10 Mar 2016 14:14:44 GMT

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,

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

sunny_talwar — Thu, 10 Mar 2016 15:01:21 GMT

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

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

vishalgoud — Sun, 13 Mar 2016 15:07:31 GMT

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,

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

sunny_talwar — Sun, 13 Mar 2016 15:57:04 GMT

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

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

vishalgoud — Tue, 15 Mar 2016 09:20:41 GMT

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,

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

sunny_talwar — Tue, 15 Mar 2016 09:25:03 GMT

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.

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

vishalgoud — Sun, 20 Mar 2016 11:03:23 GMT

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,

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

sunny_talwar — Sun, 20 Mar 2016 23:20:49 GMT

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])))

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

vishalgoud — Fri, 25 Mar 2016 06:17:36 GMT

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.