8 Replies Latest reply: Aug 15, 2017 10:06 PM by Chan HS

# set analysis without aggregation

Hi,

Is it possible to do a set analysis without aggregation?

Right now, I have a single table filled with data from different months. I want to calculate the data taken from the latest month only. Using the data from the latest month, i will check the letter of the alphabet. If it is A and more than 90 days has passed since i first found it, i will count the data. The same applies if the alphabet is B and more than 60 days has passed or if the alphabet is C and more than 30 days has passed. For now, i have come up with this expression:

Count(distinct(if([Status] = 'Open',

if([Alphabet] = 'A', if(Num(Today())>=Num([First Found]+90), [data]),

if([Alphabet] = 'B', if (Num(Today())>=Num([First Found]+60), [data]),

if([Alphabet] = 'C', if (Num(Today())>=Num([First Found]+30), [data]))

)))))

Putting this expression in a pie chart will show all the data from all the months together. However, I want to take the data from only the latest month, so i used set analysis. I have tried using set analysis to extract out the latest month of data only, however i do not need any set aggregation involved. Therefore is it possible to do set analysis without aggregation or do i have to make a new table for the latest month?

If it is possible to use set analysis without aggregation, then what am i doing wrong? In the expression below, i have added in the set analysis to the first part of the same expression but when i place the results in a table with all the other data, all i get is a -. Doing a max[Month] shows the name of my file so i think it has to do with the format or the aggregation. What am i doing wrong?

Count(distinct(if ({<[Month] = {'\$(=Max([Month]))'}>}[Status] = 'Open'),

if({<[Month] = {'\$(=Max([Month]))'}>}[Alphabet] = 'A',

if(Num(Today())>=({<[Month] = {'\$(=Max([Month]))'}>}Num([First Found]+90)), ({<[Month] = {'\$(=Max([Month]))'}>}[data]))))

For example:

I have the following data model:

 Month Alphabet Status First Found data Feb A Closed 02/05/2017 apple Feb B Open 05/07/2017 orange Aug C Open 19/12/2016 banana Jun B Open 25/02/2017 pineapple Aug A Closed 07/03/2017 banana Aug B Open 14/07/2017 pineapple Jun C Closed 12/09/2016 apple Aug B Open 15/01/2017 apple

As i will be taking only the data from the latest month, rows 3, 5, 6 and 8 will be the ones I'm focusing on. Next, I am focusing on the status, where if it is 'Open' then i will include it. Therefore, i will now only look at rows 3, 6 and 8 . Next, depending on the alphabet, i will be counting the time from today to the first found date. Now, i will have rows 3 and 8 left. (90 days on A, 60 days on B, 30 days on C). Lastly, i will count the number of data left, which is 2.

Hope it now makes sense.

Thanks!

• ###### Re: set analysis without aggregation

Perhaps like this:

Count({<[Status]={'Open'},[Month]={"\$(=Max([Month]))"}>}distinct(

if([Alphabet] = 'A', if(Num(Today())>=Num([First Found]+90), [data]),

if([Alphabet] = 'B', if (Num(Today())>=Num([First Found]+60), [data]),

if([Alphabet] = 'C', if (Num(Today())>=Num([First Found]+30), [data]))

))))

• ###### Re: set analysis without aggregation

Hi,

using the expression still gives me a -. I have updated the question to clarify on what i am looking for.

Thanks!

• ###### Re: set analysis without aggregation

Hi,

Not sure what you are looking but give it a try with below expression.

Count({<[Month] = {'\$(=Max([Month]))'}>}distinct(if([Status] = 'Open',

if([Alphabet] = 'A', if(Num(Today())>=Num([First Found]+90), [data]),

if([Alphabet] = 'B', if (Num(Today())>=Num([First Found]+60), [data]),

if([Alphabet] = 'C', if (Num(Today())>=Num([First Found]+30), [data]))

)))))

Regards,

Kaushik Solanki

• ###### Re: set analysis without aggregation

Hi,

using the expression still gives me a -. I have updated the question to clarify on what i am looking for.

Thanks!

• ###### Re: set analysis without aggregation

Hey,

Can't seem to edit my previous post. I relooked at the expression again and found out that i made a couple mistakes when copying the expression over to my app. After fixing those errors they seem to work now.

Thanks!

• ###### Re: set analysis without aggregation

Please post some sample data! and expected output

Set analysis is possible with aggregation functions only and you will only use set analysis when you have any conditions/filters to apply in your calculations.

It’s all Aggregations

• ###### Re: set analysis without aggregation

Hi,

I have updated the question to clarify on what i am looking for. Hope it now makes sense.

Thanks!

• ###### Re: set analysis without aggregation

May be this

=Sum({<MonthNum = {\$(=Max(MonthNum))}, Status = {'Open'}>}

If(Alphabet = 'A' and [First Found] < Today() - 90, 1,

If(Alphabet = 'B' and [First Found] < Today() - 60, 1,

If(Alphabet = 'C' and [First Found] < Today() - 30, 1))))