Compare two fields using Set Analysis

[#1]

Hello,

I have two fields., one is the Calmonth and the other is the SubmitMonth. These two fields are not linked. Now I have CalMonth as the dimension and I want the count of tickets submitted in that CalMonth. Instead of using If condition I want to use SetAnalysis, as the IF is taking very long time.

My expression is :

=(COUNT(DISTINCT {$<CalMonthName={"=SubMnthName"}>}
[Incident Number]))

Thanks

[jonathandienst]

Hi

You cant use set analysis for this sort of problem, as the set expression is evaluated once only, before the dimensions have been built. The options you have are sum(if()), to set a flag during the load (and set expression to pick the flagged lined), use SubmitMonth as the chart dimension, or transform your data model so that you can get the monthly totals without sum(if()). I suggest that you focus on how to do this using the last two options.

HTH

Jonathan

[datanibbler]

Hi Jebasheeba,

just a thought on alternative ways of doing this - using Set_analysis is resource-heavy and a bit tricky in charts.

Instead of doing this in the diagram, you might consider

- building a binary field (1 if months are the same, 0 otherwise) in the script (=> calculated only once, better performance)

- using a master_calendar and linking that fact table to it via the SubmitMonth.

Best regards,

DataNibbler