Skip to main content

Happy New Year everyone,

 I am working on building a List Element, and was wondering if it is possible to have it only list one site with totals for everything instead of it listing the same site for all Part Numbers, i am trying to build something for a quick view of inventory records that need to be counted, if i use the matrix element i cant get all the columns i need. below is a screen shot of my current list and my Data Source. thank you for any assistance you can provide

 

 

hi @Rpauley 

You need to group by the site (CONTRACT)

 

I get the below error message when i try to use the Group By function

 

 

You should Group By the full column name as @paul harland has highlighted. :grinning:

I did try both ways and got the same error message

 

 

you will need to revisit the other terms (the case statements)

can you show us their full contents?

(CASE WHEN LAST_COUNT_DATE >trunc(to_date( '#START_OF_THIS_YEAR#', 'YYYY-MM-DD-HH24:MI:SS' )) THEN '1' END)/100

 

(CASE WHEN LAST_COUNT_DATE <trunc(to_date( '#START_OF_THIS_YEAR#', 'YYYY-MM-DD-HH24:MI:SS' )) THEN '1' END)/100

 

(CASE WHEN LAST_COUNT_DATE >trunc(to_date( '#START_OF_THIS_YEAR#', 'YYYY-MM-DD-HH24:MI:SS' )) THEN '1' ELSE '1' END)/100

I think you can just put a sum around the first 2, like :

SUM(CASE WHEN LAST_COUNT_DATE >trunc(to_date( '#START_OF_THIS_YEAR#', 'YYYY-MM-DD-HH24:MI:SS' )) THEN '1' END)/100

The third one is trivial, you can replace it with  COUNT(*)/100  or Sum(1)/100

to make the first one a percentage i would rearrange it like this:

100 * SUM(CASE WHEN LAST_COUNT_DATE >trunc(to_date( '#START_OF_THIS_YEAR#', 'YYYY-MM-DD-HH24:MI:SS' )) THEN '1' END) / count(*)

 

@paul harland 

 I tried to update how you suggested for the first one and got the below message

 For the third one i am actually taking that one off that was a trial that i dont need if i get the other two to work.

 

take everything out so that you just have:

Group by: Contract

Column: Contract

Column: 

100 * SUM(CASE WHEN LAST_COUNT_DATE >trunc(to_date( '#START_OF_THIS_YEAR#', 'YYYY-MM-DD-HH24:MI:SS' )) THEN '1' else ‘0’ END) / count(*)

Here is what i get, looks like it worked except the percent doesnt look right

 

 

is there a way to get it to round to nearest whole number, i think that is what is throwing me off

2% i guess …

 

add a round term to tidy it up

 

round(100 * SUM(CASE WHEN LAST_COUNT_DATE >trunc(to_date( '#START_OF_THIS_YEAR#', 'YYYY-MM-DD-HH24:MI:SS' )) THEN '1' else ‘0’ END) / count(*),2)

@paul harland 

 Thank you for your help with this one greatly appreciated

Terms & ConditionsCookie settings