Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))))
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))))
The table is not changing now irrespective of the year and months selected or unselected.
But my values are completely different;
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))))
@sunny_talwar it looks like it is changing with the selections.
What selections do you have when you see this?
@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.
I am unable to attach qvd file
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))))
@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