topic Re: List Box Properties query: Two Years data in QlikView

List Box Properties query: Two Years data

knightwriter — Tue, 12 Jan 2016 11:41:43 GMT

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.

Re: List Box Properties query: Two Years data

Chanty4u — Tue, 12 Jan 2016 11:44:23 GMT

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

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

Re: List Box Properties query: Two Years data

sunny_talwar — Tue, 12 Jan 2016 12:21:12 GMT

May be this:

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

Re: List Box Properties query: Two Years data

Anonymous — Tue, 12 Jan 2016 12:30:03 GMT

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]), '###,###')

Re: List Box Properties query: Two Years data

knightwriter — Tue, 12 Jan 2016 13:30:33 GMT

Hey Sunny,

Hope you're well.

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

Thanks

Re: List Box Properties query: Two Years data

sunny_talwar — Tue, 12 Jan 2016 13:32:29 GMT

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.

Re: List Box Properties query: Two Years data

Anonymous — Tue, 12 Jan 2016 13:40:29 GMT

Did you try with my suggestion?

Re: List Box Properties query: Two Years data

knightwriter — Tue, 12 Jan 2016 14:46:37 GMT

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

Re: List Box Properties query: Two Years data

knightwriter — Tue, 12 Jan 2016 14:47:57 GMT

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

Re: List Box Properties query: Two Years data

sunny_talwar — Tue, 12 Jan 2016 14:49:24 GMT

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

Re: List Box Properties query: Two Years data

knightwriter — Tue, 12 Jan 2016 14:54:48 GMT

Hi Sunny,

Removed the extra ( but still no luck. Could it be do it with the last Max(Year) part?

Re: List Box Properties query: Two Years data

sunny_talwar — Tue, 12 Jan 2016 14:56:56 GMT

Can you share the screenshot for the whole expression. Also do you have a field named Year (check for the exact case match Year vs. YEAR)

Re: List Box Properties query: Two Years data

knightwriter — Tue, 12 Jan 2016 15:49:08 GMT

Re: List Box Properties query: Two Years data

settu_periasamy — Tue, 12 Jan 2016 15:56:06 GMT

Remove the comma in the end

>}[Activity Id])

And closed square bracket ']' missing in max activity date..

Re: List Box Properties query: Two Years data

knightwriter — Tue, 12 Jan 2016 16:37:29 GMT

still no luck!

Maybe better to rethink.

I am trying to split # of activities by quarter and depending on year the user has selected........

Re: List Box Properties query: Two Years data

sunny_talwar — Tue, 12 Jan 2016 16:39:04 GMT

I would also check if the field name is Year or Activity.Year or some other variation because that is where the expressions seems to be going wrong from.