Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
bhaveshp90
Contributor III

How to detach in Set analysis

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.)

1.PNG

 = 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

Labels (4)
1 Solution

Accepted Solutions

Re: How to detach in Set analysis

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))))
11 Replies

Re: How to detach in Set analysis

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
Contributor III

Re: How to detach in Set analysis

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

But my values are completely different;

Capture.PNG 

 

Re: How to detach in Set analysis

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
Contributor III

Re: How to detach in Set analysis

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

Re: How to detach in Set analysis

What selections do you have when you see this?

image.png

bhaveshp90
Contributor III

Re: How to detach in Set analysis

@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
Contributor III

Re: How to detach in Set analysis

I am unable to attach qvd file Smiley Sad

Re: How to detach in Set analysis

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
Contributor III

Re: How to detach in Set analysis

@sunny_talwar Awesome Smiley Very Happy . 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