How to detach in Set analysis

[bhaveshp90]

Hello, 

I have an expression below to calculate the delay days as shown below; (I want to permanently lock this table so I have used {1} in my expression.)

 = if(Sum(Final_Acceptance)>3500, null(),

(Sum({1<Snapshot_Date = {">=$(=Date(Addmonths(Max({1} Snapshot_Date),0), 'YYYY-MM-DD'))"}>}Final_Acceptance)
/
(count({1<Snapshot_Date = {">=$(=Date(Addmonths(Max({1} Snapshot_Date),-1), 'YYYY-MM-DD'))"}>}distinct Milestone_Project_Number))))

This expression is working irrespective of year selected, but when I unselect month the table is changing. 

Any idea how to fix this? 

PS: Year(Date) as Year and Month(Date) as Month 

thanks

Bhavesh

[sunny_talwar]

May be you need {1} here also

=If(Sum({1} Final_Acceptance)>3500, Null(),

(Sum({1<Snapshot_Date = {">=$(=Date(Addmonths(Max({1} Snapshot_Date),0), 'YYYY-MM-DD'))"}>}Final_Acceptance)
/
(count({1<Snapshot_Date = {">=$(=Date(Addmonths(Max({1} Snapshot_Date),-1), 'YYYY-MM-DD'))"}>}distinct Milestone_Project_Number))))

[bhaveshp90]

The table is not changing now irrespective of the year and months selected or unselected. 

But my values are completely different;

 

[sunny_talwar]

Seems like you only wish to ignore selection in Month and Year... may be try this

=If(Sum({<Month, Year>} Final_Acceptance)>3500, Null(),

(Sum({1<Snapshot_Date = {">=$(=Date(Addmonths(Max({1} Snapshot_Date),0), 'YYYY-MM-DD'))"}>}Final_Acceptance)
/
(count({1<Snapshot_Date = {">=$(=Date(Addmonths(Max({1} Snapshot_Date),-1), 'YYYY-MM-DD'))"}>}distinct Milestone_Project_Number))))

[bhaveshp90]

@sunny_talwar it looks like it is changing with the selections. 

[sunny_talwar]

What selections do you have when you see this?

[bhaveshp90]

@sunny_talwar I am selecting Year and month as per your image. 

When I toggle between a year or clear year it remains static. 

 But when I toggle between month it is static, it is changing when we clear month field. I have attached sample qvw for your reference. 

[bhaveshp90]

I am unable to attach qvd file 

[sunny_talwar · Accepted solution]

Try this

=If(Sum({1<Month,Snapshot_Date = {">=$(=Date(Addmonths(Max({1} Snapshot_Date),0), 'YYYY-MM-DD'))", ">=$(=Date(Addmonths(Max({1} Snapshot_Date),-1), 'YYYY-MM-DD'))"}>} Final_Acceptance)>3500, Null(),

(Sum({1<Month,Snapshot_Date = {">=$(=Date(Addmonths(Max({1} Snapshot_Date),0), 'YYYY-MM-DD'))"}>}Final_Acceptance)
/
(count({1<Month, Snapshot_Date = {">=$(=Date(Addmonths(Max({1} Snapshot_Date),-1), 'YYYY-MM-DD'))"}>}distinct Project_Number))))

[bhaveshp90]

@sunny_talwar Awesome  . This is exactly what I want, thank you so much.

Can you explain me this condition,

If(Sum({1<Month,Snapshot_Date = {">=$(=Date(Addmonths(Max({1} Snapshot_Date),0), 'YYYY-MM-DD'))", ">=$(=Date(Addmonths(Max({1} Snapshot_Date),-1), 'YYYY-MM-DD'))"}>} Final_Acceptance)>3500