6 Replies Latest reply: Mar 19, 2014 3:32 PM by Neena Bhattarai RSS

    Am I using the proper function for this problem?

    Neena Bhattarai

      I have four facilities that are joined together under the dimension name Facility. In order to separate each facility to show the proper data I have to write the expression like this:

       

      count(Distinct if ([Turnaround Days] <=0 AND [Status]='Shipped' AND  [Facility]='Alpharetta',[Unique Order key], null()))

       

      That expression does work for the individual charts. However I want each facility to have two percent under each chart showing this year turnaround day times that are over 3+ days and last year 3+ turnaround days. Then I would like a text object like an arrow pointing up or down if the number has gotten higher or lower. This is how I have the expression written:

       

      =count(distinct if([Turnaround Days]>2 AND [Facility]='Alpharetta' AND [Status}='Shipped', [Unique Order Key], null()))/count(Distinct [Unique Order Key])

       

      It is not giving me the average of the Alpharetta facility it is dividing it by ALL of the facilities turnaround Days that is over 2 but I want it to just stay in the facility I have assigned it too. So I started to write the expression like this:

       

      =count(distinct if([Turnaround Days]>2 AND [Facility]='Alpharetta' AND [Status]='Shipped, [Unique Order Key], null()))/count(Distinct [Unique Order Key] AND [Facility]='Alpharetta')

       

      But it doesn't give me a percent just a random number that I know is not correct.

      I have attached a photo of the layout. The blue box is the percent of Turnaround Days >2. I have not set the year yet either and I need to know how to incorporate that into the expression as well. I want two percent under each facility like I had said before one for current year and one for last year.

      How can I make this work?