set analysis without aggregation

[#1]

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!

[Gysbert_Wassenaar]

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]))

))))

[kaushiknsolanki · Accepted solution]

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

[vinieme12]

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

[#5]

Hi,

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

Thanks!

[#6]

Hi,

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

Thanks!

[#7]

Hi,

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

Thanks!

[sunny_talwar]

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))))

[#9]

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!