Set Analysis IF Statement different levels

[Ja123__]

Hi Everyone

I posted something a while ago where inside a calculation I needed to filter at a higher level (less filters) than the limits that was in my set analysis calculation and this was resolved with the use of TOTAL and filters/ <>. 

In this new scenario I have a table I am creating, if I can get this example right I can replicate for this section.

I have a table where I have txtcategories. I then need to bring in rates. The general hierarchy in my model is ClientParentID/ClientID/MemberID. I use max as an aggregation in the example, the set analysis is the important part.

max(
{<
fkiClientDefaultCategoryID = {"=FirstSortedValue(RuleID,-dteEffectiveDate)"}
>}
numEERate25 )

The model will always be filtered to one ClientID. If I just drop =FirstSortedValue(RuleID,-dteEffectiveDate) as a measure next to the txtCategory, then yes it is correct value. And the ClientID that the user has been selected in the model will limit the results correctly. Now that value that comes as the result I need to use to filter another field called fkiClientDefaultCategoryID, which will leave me with one row left in the model and it will contain all the correct values including numEERate25.

Now when I do the next step and filter on another field fkiClientDefaultCategoryID which is very important, I need it to bring back a result that is not limited by the ClientID (or any ClientID's) that the user has selected. First of all, I'm not sure if I have scripted it correctly above. But also I need to bring in a rule that now will bring back the numEERate25 from the overall model with no filters.

So in short the {"=FirstSortedValue(RuleID,-dteEffectiveDate)"} must be filtered by the user ClientID selections, but the "fkiClientDefaultCategoryID =" and "numEERate25 " parts must not be filtered by user selections on ClientID.

Can anyone give me some direction I don't seem to be getting this right and have experimented with "1" in set analysis but I think I'm missing mores steps in this case.

Many thanks,

James

[JustinDallas]

Do you have any dummy data that we can fiddle with?

[Ja123__]

Good morning Justin

I quickly put together a test example as the actual model is a bit busy and 300MB.

My final result I want is:

Column 1: Most recent txtCategories applicable

Column 2 - 4: Rate 1,2,3

How the client's rule works is:

Filter for ClientID "1". List all txtCategories that are <= 31 December 2018 for ClientID 1 using dteEffectiveDate. That will give u txtCategories that are relevant.

Now, for those txtCategories. Take the RuleID we calculated. That ruleID represents a "fkiClientDefaultCategoryID", which is in the MemberFund table but doesn't have a ClientID attached to it. Hence my whole forum post...

If you limit on the "fkiClientDefaultCategoryID", you get the default MemberID which has the default rates for the txtCategory. Once you have that default MemberID, you limit for the default member and you use those Rates details in the rates table for the Category. 

Quite a mouthful but I hope that helps... Apologies if it is confusing at first.

If it wasn't for me having to work with dteeffective dates etc on the front end I would just calculated all of this once off in the backend...

James

[lironbaram]

hi 

going through your post , 

after i select a client do you expect to get a list of text categories for the last date of the selected client , and the correspondent rule to each category ?