Set Analysis with subset filter

[#1]

Hi,

I am turning to the forum as I have exhausted all my investigation on this subject. I have a requirement to count a number of distinct values where specific values in a previous piece of set analysis have occured. In essence a subset of values to be part of the where clause. Below is the code I am trying to do, I think I am missing a symbol or something but sure I am very close to the solution:

=count(distinct {$<
 [Posted Year] = {$(#=year(AddMonths(makedate(Year,Month,01),0)))},
 [Posted Month] = {$(#=month(AddMonths(makedate(Year,Month,01),0)))},
 [Line Number] = {2},
 linkVendorInvoiceHeader =
 {"$
 (= chr(34) & concat(
 {<
 [Posted Year] = {$(#=year(AddMonths(makedate(Year,Month,01),0)))},
 [Posted Month] = {$(#=month(AddMonths(makedate(Year,Month,01),0)))},
 [Line Number] = {1},
 IncludedInFirstTimePass = {1}
 >} distinct linkVendorInvoiceHeader,
 chr(34) & ',' & chr(34)) & chr(34)
 "}>}

 linkVendorInvoiceHeader)

The specific area of the subset when run seperatley works as expected, heres the code on its own:

= chr(34) & concat(
 {<
 [Posted Year] = {$(#=year(AddMonths(makedate(Year,Month,01),0)))},
 [Posted Month] = {$(#=month(AddMonths(makedate(Year,Month,01),0)))},
 [Line Number] = {1},
 IncludedInFirstTimePass = {1}
 >} distinct linkVendorInvoiceHeader,
chr(34) & ',' & chr(34)) & chr(34) 

This returns values like this:

"a1","a2","a3","a4"

So when I manually copy and paste the text results into the top code example the figures return. But it doesnt work dynamically. If anyone guru's out there know what is syntaxtically wrong with my code please help

Thanks,

Jon

[Miguel_Angel_Baeyens · Accepted solution]

Hello Jon,

Then you are double double quoting if I'm not wrong (remove double quotes where the bold brackets):

Interserve wrote:=count(distinct {tiny_mce_markerlt;<blockquote><pre> [Posted Year] = {$(#=year(AddMonths(makedate(Year,Month,01),0)))},
 [Posted Month] = {$(#=month(AddMonths(makedate(Year,Month,01),0)))},
 [Line Number] = {2},
 linkVendorInvoiceHeader =
 {$
 (= chr(34) & concat(
 {<
 [Posted Year] = {$(#=year(AddMonths(makedate(Year,Month,01),0)))},
 [Posted Month] = {$(#=month(AddMonths(makedate(Year,Month,01),0)))},
 [Line Number] = {1},
 IncludedInFirstTimePass = {1}
 >} distinct linkVendorInvoiceHeader,
 chr(34) & ',' & chr(34)) & chr(34)
 }>}

 linkVendorInvoiceHeader)

Hope that helps

[#3]

Hi Miguel,

Thanks for the quick responce! Your code does not show bold but take it you mean removing the double quotes from linkVendorInvoiceHeader = {"$.

I did that but rather than a "0" being displayed in the results I now get "-" which tells me there is a problem...

[#4]

Hello again Miguel,

Ignore my last quick reply, I have success! Thanks for the prompt on the quotes. For the benefit of anyone reading this here is the solution. It was pesky brackets at fault!

=count(distinct {$<
 [Posted Year] = {$(#=year(AddMonths(makedate(Year,Month,01),0)))},
 [Posted Month] = {$(#=month(AddMonths(makedate(Year,Month,01),0)))},
 [Line Number] = {2},
 linkVendorInvoiceHeader = {$(= chr(34) & concat(
 {<
 [Posted Year] = {$(#=year(AddMonths(makedate(Year,Month,01),0)))},
 [Posted Month] = {$(#=month(AddMonths(makedate(Year,Month,01),0)))},
 [Line Number] = {1},
 IncludedInFirstTimePass = {1}
 >} distinct linkVendorInvoiceHeader,
 chr(34) & ',' & chr(34)) & chr(34))

 }>} linkVendorInvoiceHeader)

[jonathandienst]

Hi Your original expression may be correct, but I think you want to enclose the characters returned from the inner set with single quotes, rather than double.

In other words, change chr(34) to chr(39).

Hope that helps

Jonathan