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

    set analysis without aggregation

    Chan HS



      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:

      MonthAlphabetStatusFirst Founddata




      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.