Set Analysis Expression is not working

[Anonymous]

Hi all,

I want to write this if expression in Set Analysis -

IF Expression:

(count(distinct if(PostingAction = 'ADD' and ExpiresDate>=AsOfDate, JobBoardJobId))) (Expression is working fine)

Set Analysis Expression :

count({<PostingAction = {'ADD'} , ExpiresDate={">=AsOfDate"}>} DISTINCT JobBoardJobId) (Expression is not working)

I am trying to implement the same in Set Analysis but its not working.

Note : AsOfDate is a field name. In Data Model, AsOfDate field is in one table , Expires Date and Action Date is in other table. Hence couldn't create a flag in data model in this case. That's why trying to implement it using Set Analysis on Front End.

Can anyone help me where I am doing wrong

Regards,

Nisha

[sunny_talwar]

Is AsOfDate a variable or fieldname? If it is a field, then it seems you are looking for a row wise evaluation which isn't going to work for you because set analysis is evaluated once per chart.

[Anonymous]

may be try like this at script?

Load

*,

If(ExpiresDate>=AsOfDate,1,0) as Flag

From Table;

then write this expression:

=count({<PostingAction = {'ADD'} , Flag={'1'}>} DISTINCT JobBoardJobId)

[Anonymous]

I have updated my post now. As Of Date is a field name.

[Anonymous]

AsOfDate is a field name. In Data Model, AsOfDate field is in one table , Expires Date and Action Date is in other table. Hence couldn't create a flag in data model in this case. That's why trying to implement it using Set Analysis on Front End.

[Anonymous]

may be like this?

Set DateFormat='DD/MMM/YYYY'; in script

Set Analysis Expression :

count({<PostingAction = {'ADD'} , ExpiresDate={">=$(=date(AsOfDate))"}>} DISTINCT JobBoardJobId)

make sure both should have same format!

[sunny_talwar · Accepted solution]

Based on your description, you will probably need to stick with if statement. May be like this:

Count(DISTINCT I{<PostingAction = {'ADD'}>} If(ExpiresDate>=AsOfDate, JobBoardJobId))

[Anonymous]

No, Its not working when it compares row wise Dates. Thanks for your suggestion.

If I select one date, that time it will work.