Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

vishalgoud
Contributor III

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

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.

1 Solution

Accepted Solutions

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

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

Capture.PNG

2) 2 month selection in global filter

Capture.PNG

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

Capture.PNG

4) 2 month selection in state2 filter

Capture.PNG

20 Replies

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

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

vishalgoud
Contributor III

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

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,

V.

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

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?

vishalgoud
Contributor III

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

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,

V.

Digvijay_Singh
Honored Contributor III

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

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....?

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

vishalgoud
Contributor III

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

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....?

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

vishalgoud
Contributor III

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

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,

V.