Need to sum a field based on distinct values as well as using set analysis to pull bad prior year?

[#1]

Hi all.

I'm pretty new to using set analysis' and aggr functions etc so I'm really running in circles now. 

I have an OR_Log_Key which has duplicates, so i know that I need to use distinct to pull them apart.. thats fine.

My issue is that in the table I'm making I am showing current FY vs previous FY.  And I'm lost on how to sum up a column based on distinct ID as well as the function to pull back FY vs prior FY.

This is the def of what I used for counting up my distinct ID's, which works:

count(distinct {<[Surgery Start Date FY Year]={$(=Max([Surgery Start Date FY Year])-1)}>}[OR Log Key])

however, when trying to combine that in to some sort of sum/aggr function it fails miserably.  I simply don't know how to combine the above, with the sum or aggr functions in order to get a sum of the field Case_Minutes.

I know the following doesnt work!

sum(aggr(sum(distinct {<[Surgery Start Date FY Year]={$(=Max([Surgery Start Date FY Year])-1)}>}[OR Log Key]), [Case Minutes])

Could someone point me in the right direction or help me through this.

Much appreciated!

Stan

P.S. I plan on being quite a nusiance on here until I get my bearings in this app.. qlikview is like learning a new language when it comes to all the built in tricks on top of the functions!

[kaushiknsolanki]

Hi,

     Kindly post your application for more clarification.

Regards,

Kaushik Solanki

[#3]

Alright.. on tab "Minutes by Month" in the first straight table the expression for Column 5 is what i am having issues with.  Basically the whole table is a current FY vs Prior FY counts/sums with differences and variances.

I also have another issue with the data table under my chart in Cases By Month tab.. using the accumulation check on fields to get the YTD counts I can't get a variance for it in the last formula either.. you can't simpy subtract column from column due to the accumulation?

Thanks

stan