List Box Properties query: Two Years data

[knightwriter]

Hi All,

I originally had list box properties to pull 2015 data only. I want it now to show 2015 or 2016 depending on what Year filter is selected. I have used the below script with the bold being the 2015 information to  be included but the result does not seem correct to me.

Any suggestions? Or any way I can reduce the below ?

=Num (Count(Distinct If([Company.Car Order]='Ford' AND [Status]='Completed' AND InYear([Completed Date], Today(), 0) or InYear([Completed Date], Today(), -1), [Id], )), '###,###')

Many thanks.

[Chanty4u]

=Num (Count(Distinct If([Company Car Order]='Ford' AND [Status]='Completed' AND

InYear([Completed Date], Today(), 0) or InYear([Completed Date], Today(), -1) Id)) '###,###')

[sunny_talwar]

May be this:

=Count(DISTINCT {<[Company.Car Order] = {'Ford'}, Status = {'Completed'}, Year = {$(=Max(Year)}>} Id)

[Anonymous]

No harm trying like this?

Take two variable:

Let V1=max(YearField)

Let V2=max(YearField)-1

=num(Count(Disctinct {<[Company.Car Order]={'Ford'} , [Status]={'Completed'}, YearField={'>=$(=V2)<=$(=V1)'}>} [Id]), '###,###')

[knightwriter]

Hey Sunny,

Hope you're well.

Will Year = {$(=Max(Year)}>} show all years? I only want it to show '15 & '16.

Thanks

[sunny_talwar]

No, it will only show the max year. If nothing is selected and 2016 is the max year than it will show the data for 2016. If you make a selection on 2015, max year changes to 2015 and you will see the results for 2015.

[Anonymous]

Did you try with my suggestion?

[knightwriter]

Ah great, that worked a charm!

If I apply the same principle to use the InQuarter function I get "Error", whats wrong with the below?

=Count(DISTINCT {<[Activity.Order] = {'Completed'},{<[Activity.Order Date]= {'InQuarter'}, Year = {$(=Max(Year)}>}, [Activity Id])

[knightwriter]

Hi Balrai, I tried it momentarily and I couldnt get it working for me so I reverted to another suggestion. Thanks though.

[sunny_talwar]

You seems to have extra {< in there:

Count(DISTINCT {<[Activity.Order] = {'Completed'},{<[Activity.Order Date]= {'InQuarter'}, Year = {$(=Max(Year)}>}, [Activity Id])

Try removing it:

Count(DISTINCT {<[Activity.Order] = {'Completed'}, [Activity.Order Date]= {'InQuarter'}, Year = {$(=Max(Year)}>}, [Activity Id])