Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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]))
))))
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
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.
Hi,
using the expression still gives me a -. I have updated the question to clarify on what i am looking for.
Thanks!
Hi,
using the expression still gives me a -. I have updated the question to clarify on what i am looking for.
Thanks!
Hi,
I have updated the question to clarify on what i am looking for. Hope it now makes sense.
Thanks!
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))))
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!