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

    set analysis without aggregation

    Chan HS

      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:

      MonthAlphabetStatusFirst Founddata
      FebAClosed02/05/2017apple
      FebBOpen05/07/2017orange
      AugCOpen19/12/2016banana
      JunBOpen25/02/2017pineapple

      Aug

      AClosed07/03/2017banana
      AugBOpen14/07/2017pineapple
      JunCClosed12/09/2016apple
      AugBOpen15/01/2017apple

       

      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!