"WHERE condition" in Set Analysis when calculating YTD figures?

[#1]

The source table contains monthly snapshots.

If I want to see New Business (criteria for New Business is POLICY_INCEPTION_DATE in chosen SNAPSHOT_MONTH), this bellow works fine regardless how many months I choose:

= count(distinct
(if (([INDICATOR] = 'A' AND [Date] >= vMinDateCurrSelection AND [Date] <= vMaxDateCurrSelection AND [POLICY_INCEPTION_MNTH] = [Month] AND POLICY_INCEPTION_YEAR = [Year]), [Policy No])
)
         )

Please note: [Date], [Month] and [Year] are taken from DIM_DATE where fact.SNAPSHOT_DT_SKY_NO is linked to DIM_DATE.DATE_SKY_NO

Because I have to do comparison with previous year period, I created similar Set Analysis expression bellow:

= COUNT({$<[Date]={">=$(vMinDateCurrSelection)<=$(vMaxDateCurrSelection)"}, [INDICATOR] = {A}, [POLICY_INCEPTION_MNTH] = [Month], POLICY_INCEPTION_YEAR = [Year]>} DISTINCT [Policy No])

, but this expression doesn’t work if a user chooses more than 1 month. It looks that Set Analysis in not evaluating highlighted part on a record level. What it does, it, as well, adds records from May’s partition where policy_inception_date is in Apr (if I choose Apr & May snapshot), which is wrong because that is not New Business in May.

I have tried to use AGGR function as well but got really bad performance issues (am not sure where I went wrong with AGGR…)

I think Set Analysis would be the most elegant solution but don’t know if something is wrong in my expression or that is how set analysis works.

Please could you give us a suggestion how to move on.

TIA

SnezAna

[Gysbert_Wassenaar · Accepted solution]

I think the problem is that you're trying to compare fields of the same record. Set analysis calculates one set per chart, not a set per row. See this document for an explanation.

[#3]

Brilliant, thanks so much!

Update:

The solution is correct but I am struggling to move forward. I managed to get correct figures for bellow expression:

= COUNT({<DM_FCT_POLICY_PREMIUM_MONTHLY_id= {'=[POLICY_INCEPTION_MNTH] = [Month]'} >} [Policy No])

In the above expression I am missing to specify which snapshot to be chosen and to include  [POLICY_INCEPTION_YEAR] = [Year]

E. g. SNAPSHOT_DT={">=$(=Date(vMinDateCurrSelection))<=$(=Date(vMaxDateCurrSelection))"},  [POLICY_INCEPTION_YEAR] = [Year]>}

And I am so strugling with the syntax... Why in the purposed solution we are having single quotes?

And why do we have '=' sign highlighted in PINK?

I coudn't find a good document explaining Set Analysis syntax... How to fully understand the syntax and make sure I know what I am doing without guessing ?

Also, after I have impelemented purposed expression performance is significantly worse?

Any suggestion?

[#4]

Got correct figures using intersection (as bellow):

=

COUNT(
{$<
SNAPSHOT_DT={">=$(=Date(vMinDateCurrSelection))<=$(=Date(vMaxDateCurrSelection))"},
DM_FCT_POLICY_PREMIUM_MONTHLY_id={'=[POLICY_INCEPTION_MNTH] = [Month]'}*{'=[POLICY_INCEPTION_YEAR] = [Year]'}
>} [Policy No])

Performance is not good.

Found book on Amazon that I beleive I should go through.