18 Replies Latest reply: Oct 31, 2017 2:57 AM by siddharth soam

# How to read multiple values from a filter pane

Hi

Please  tell me how can I fetch multiple values from a filter pane . I have a accounting period filter from which I want to calculate a certain measure of the previous selected period.

For example if I select counting period as 4,5 it should return me sum(revenue) for 4 and 3 month.

• ###### Re: How to read multiple values from a filter pane

Try this

=Sum({<[counting period]={\$(=Concat(Distinct [counting period]-1,','))}>}revenue)

• ###### Re: How to read multiple values from a filter pane

Hi Dave,

Its working well only its not giving me any data for 1 counting period

• ###### Re: How to read multiple values from a filter pane

I am using the following expression

if(H1='Previous Completed Quarter', Sum({<Quarters={\$(=Concat(Distinct Quarters-1,','))} ,[Accounting Period]={\$(=Concat(Distinct [Accounting Period]-3,','))}>}[Service Rev]),

if(H1='Recent Completed Quarter',sum({\$}[Service Rev]))

))

where h1 is the header  and quarters are fetched from accounting period as follows.

quarters accounting period

1               1,2,3

2               4,5,6

3               7,8,9

Problem is data is showing correctly for quarter>1 , but for quarter 1 no data is being shown

• ###### Re: How to read multiple values from a filter pane

because when you select quarter 1 it will calculate 'quarter 0' which is not exist

what data do you want to show for 1 counting period ?

• ###### Re: How to read multiple values from a filter pane

Hi Dave this is the view I am trying to create , yes I agree that previous data for quarter 1 should not reflect, but it should show me quarter 1 data under recent completed quarter.

• ###### Re: How to read multiple values from a filter pane

I don't think 'H1' makes sense,you may need to get rid of it ,just go with two expressions like this

expression for previous completed quarter :

Sum({<Quarters={\$(=Concat(Distinct Quarters-1,','))}[Service Rev])

expression for recent completed quarter :

Sum([Service Rev])

• ###### Re: How to read multiple values from a filter pane

Hi Dave,

My requirement consists of multiple headers thus I have to make inline headers. I cannot use differnet measures for previous and current.

• ###### Re: How to read multiple values from a filter pane

ok , then we can combine the two expressions and try

IF(H1='Previous Completed Quarter',Sum({<Quarters={\$(=Concat(Distinct Quarters-1,','))}[Service Rev]),Sum([Service Rev]))

you may need to attach your qvf file if it still doesn't work

• ###### Re: How to read multiple values from a filter pane

Qvf in attachments.

• ###### Re: How to read multiple values from a filter pane

sorry , I typed wrongly ,this would be ok

• ###### Re: How to read multiple values from a filter pane

Quarter 1 data is still not showing up . Don't know how to proceed. Expected output should be like this.

• ###### Re: How to read multiple values from a filter pane

Thanks dave looked into the wrong file..:)... But not able to change data by altering accounting period, when I change the expression to this

IF(H1='Recent Completed Quarter' ,Sum([Service Rev]),Sum({<Quaters={\$(=Concat(Distinct Quaters-1,','))}, [Accounting Period]={\$(=Concat(Distinct [Accounting Period]-3,','))}>}[Service Rev]))

No data shows up under Q1 . Using your expression I am able to get Q1 under recent but when I change the accounting period(month) under a quarter ,it shows null value. I want that when I select quarter Q2 and accounting period 4,5 under recent ,it shows me quarter 1 , accounting period 1,2 under previous header.

• ###### Re: How to read multiple values from a filter pane

oh ~ I got you see this

btw , if you don't select anything ,"pervious completed quarter" will show Q1+Q2+Q3 data

• ###### Re: How to read multiple values from a filter pane

Thanks Lou Needed this desperately ...

• ###### Re: How to read multiple values from a filter pane

Hi Lau,

I am also having some trouble putting in condition for displaying year-2016 Quarter 4 data in previous when user selects  fiscal year-2017 and quarter 1. Can you tell me where I have to put the if condition ?

• ###### Re: How to read multiple values from a filter pane

Well , I see

this will be quite different from what we did, I think we'd better figure it out relying on a better data set

so plz attach your data source file which named "FY16.xlsx" and "FY17.xlsx" in my view

• ###### Re: How to read multiple values from a filter pane

Hi Lau,

Got it after trying certain possibilities , simple if condition outside our measure made it to work

• ###### Re: How to read multiple values from a filter pane

maybe this:

=sum( {< [counting period] = {">=\$((=min([counting period])-( GetSelectedCount([counting period])-1 ) ))<=\$(=min([counting period]))"} >} revenue )