If vs Set Analysis Using Dimensions

[timothyj]

Pretty sure this is not possible, but wanted to double check.

My chart expression is:

=Sum(If([END DATE]>[MONTH YEAR] And [START DATE]<=[MONTH YEAR],[MEMBERSHIP FEE],0))

Making a chart where [MONTH YEAR] is the dimension to track memberships over time. This works. I cannot do this in Set Analysis, can I? 

A similar count expression that will work:

=Count(If([END DATE]>[MONTH YEAR] And [START DATE]<=[MONTH YEAR], [MEMBERSHIP NUM]))

Again, there is not a Set Analysis expression that will work.

Googling the forum suggests this is because Set Analysis only calculates once per chart?

Thanks! 

[Taoufiq_Zarra · Accepted solution]

@timothyj  normally yes Set Analysis only calculates once per chart ! but there is an alternative if I understood well the model.

suppose I have this sample, I know you have dates, it's the same logic.

if I use your expression with if I have the output :

I can get the same results with this set analysis :

=Sum({<[MEMBERSHIP ID]={"=[END DATE]>[MONTH YEAR] And [START DATE]<=[MONTH YEAR]"}>} [MEMBERSHIP FEE])

output :

[timothyj]

With more data it does not work. Unsure why. I thought along those lines.

[Taoufiq_Zarra]

@timothyj  can you share the sample in Excel format for example ?

[timothyj]

This might view better.Might work better.

[timothyj]

LOAD * INLINE [
MEMBERSHIP ID, END DATE, MONTH YEAR, START DATE, MEMBERSHIP FEE
101, 2/1/2001, 1/1/2001, 1/1/2001, 10
102, 5/1/2001, 1/1/2001, 1/1/2001, 10
103, 5/1/2001, 1/1/2001, 1/1/2001, 10
104, 2/1/2001, 1/1/2001, 1/1/2001, 10
102, 5/1/2001, 2/1/2001, 1/1/2001, 11
103, 5/1/2001, 2/1/2001, 1/1/2001, 11
105, 3/1/2001, 2/1/2001, 2/1/2001, 11
102, 5/1/2001, 3/1/2001, 1/1/2001, 12
103, 5/1/2001, 3/1/2001, 1/1/2001, 12
106, 4/1/2001, 3/1/2001, 3/1/2001, 12
107, 5/1/2001, 3/1/2001, 3/1/2001, 12
102, 5/1/2001, 4/1/2001, 1/1/2001, 13
103, 5/1/2001, 4/1/2001, 1/1/2001, 13
107, 5/1/2001, 4/1/2001, 3/1/2001, 13
108, 5/1/2001, 4/1/2001, 4/1/2001, 13
];

[Kushal_Chawda · Accepted solution]

@timothyj  to work this properly in set analysis you may need to evaluate your set expression on primary key of your data. So if you already have primary key field in your data you can refer to that in set analysis like below

=Count({< Primary_Key ={"=[END DATE]>[MONTH YEAR] And [START DATE]<=[MONTH YEAR]"}>}[MEMBERSHIP NUM])

If you don't have primary key and unsure of what is the primary key then you can simply create the key in load script with simple rowno() function like below

LOAD ...

rowno() as Primary_Key

FROM table;

You can now refer this primary key in set analysis as same way described above. You can similarly use it for sum function as well

=sum({< Primary_Key ={"=[END DATE]>[MONTH YEAR] And [START DATE]<=[MONTH YEAR]"}>}[MEMBERSHIP FEE])

[timothyj]

Bingo, that made Taoufiq 's formula work.  I will add that to the much larger data set that loops through way to many months.

Who gets the correct solution tag? We have a joint effort! More questions!

[Kushal_Chawda]

@timothyj  you can mark as many replies as solution and it's your choice which one to mark. 😊