set analysis syntax

[#1]

hi

i am trying to write this code:

COUNT ({$<FACT_WINS_i_end_function_code={'1'},FACT_WINS_End_Date < $(SlectionDate) >}distinct Mismach)

i want to count 'Mismach' where the code of FACT_WINS_i_end_function_code is 1 and the date field "FACT_WINS_i_end_function_code" is smaller then the veraibe "SlectionDate".

i have tried alot of different ways without a success.

pls give me a hand...

thanks

[johnw · Accepted solution]

Assuming SlectionDate and FACT_WINS_End_Date have the same format, I think this:

count({<FACT_WINS_i_end_function_code={'1'}
,FACT_WINS_End_Date={"<$(=SlectionDate)"}>} distinct Mismach)

[Miguel_Angel_Baeyens]

Hello,

Try the following

Count({< FACT_WINS_i_end_function_code = {1}, FACT_WINS_End_Date = {'<$(=Date(SlectionDate))'} >} DISTINCT Mismach)

Make sure FACT_WINS_End_Date and Date(SlectionDate) return the same format of date, otherwise that will never return the correct results.

Hope that helps.

[#4]

Hi elad,

Have you tried COUNT ({$<FACT_WINS_i_end_function_code={'1'},FACT_WINS_End_Date < "$(date(SlectionDate))" >}distinct Mismach), i.e. the SelectionDate encapsulated in a date function and then since you are returning a varchar string, the value has to be between double-quotes.

A good way of troubleshooting what is being passed through your set analysis statement is to create a straight table with the set analysis expression and no label for the expression. Then when you come back to the object and hover your mouse over the column header (where the label should be), it will show you a floating window with the full set analysis statement.

This might either be a date/time format issue or that you are not putting your varchar between double-quotes (i.e. "value").

If this doesn't work, can you create a small inline load of data and share your app, you're almost there from seeing what you have put as your expression.

[#5]

hi

thank you all for your help.

the syntax now works and i have data but as you say i need to chek them again.

you realy help me

elad