Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
Partner
Partner

Am I using the proper function for this problem?

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?

1 Solution

Accepted Solutions

Re: Am I using the proper function for this problem?

Your first expression can be written better using set analysis, and then we can also fix the second part:

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

View solution in original post

6 Replies

Re: Am I using the proper function for this problem?

Your first expression can be written better using set analysis, and then we can also fix the second part:

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

View solution in original post

Partner
Partner

Re: Am I using the proper function for this problem?

THANK YOU!

Partner
Partner

Re: Am I using the proper function for this problem?

Thank you for helping me. I have another question I have been trying to make the expression show current year and previous year how would I incorporate that into the expression? Is their a current year function?

Re: Am I using the proper function for this problem?

Current Year:

=count({<[Turnaround Days]={'>2'},[Facility]={'Alpharetta'},[Status]={'Shipped'},[Date Field]={'=year([Date Field])=year(today()))'}>}distinct [Unique Order Key])/count({<[Facility]={'Alpharetta'},[Date Field]={'=year([Date Field])=year(today()))'}>}Distinct [Unique Order Key])

Previous Year:

=count({<[Turnaround Days]={'>2'},[Facility]={'Alpharetta'},[Status]={'Shipped'},[Date Field]={'=year([Date Field])=year(today())-1)'}>}distinct [Unique Order Key])/count({<[Facility]={'Alpharetta'},[Date Field]={'=year([Date Field])=year(today())-1)'}>}Distinct [Unique Order Key])

Note that [Date Field] cannot be a dimension on your chart.

Partner
Partner

Re: Am I using the proper function for this problem?

This is what I have: =count({<[Turnaround Days]={'>2'}, [Facility]={'Alpharetta'}, [Ship Year]=('today())-1', [Status]={'Shipped'}>}distinct[Unique Order Key])/count({<[Facility]={'Alpharetta'}>}Distinct [Unique Order Key])

Partner
Partner

Re: Am I using the proper function for this problem?

lol I was writing a reply before I saw this. Thank you so much for your time!!