topic Re: How to detach in Set analysis in QlikView

How to detach in Set analysis

bhaveshp90 — Sat, 16 Nov 2024 21:46:52 GMT

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

Re: How to detach in Set analysis

sunny_talwar — Tue, 04 Dec 2018 14:06:57 GMT

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

Re: How to detach in Set analysis

bhaveshp90 — Tue, 04 Dec 2018 14:13:24 GMT

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

But my values are completely different;

Re: How to detach in Set analysis

sunny_talwar — Tue, 04 Dec 2018 14:19:23 GMT

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

Re: How to detach in Set analysis

bhaveshp90 — Tue, 04 Dec 2018 16:12:19 GMT

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

Re: How to detach in Set analysis

sunny_talwar — Tue, 04 Dec 2018 16:17:21 GMT

What selections do you have when you see this?

Re: How to detach in Set analysis

bhaveshp90 — Tue, 04 Dec 2018 17:00:09 GMT

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

Re: How to detach in Set analysis

bhaveshp90 — Tue, 04 Dec 2018 17:02:07 GMT

I am unable to attach qvd file

Re: How to detach in Set analysis

sunny_talwar — Tue, 04 Dec 2018 17:28:46 GMT

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

Re: How to detach in Set analysis

bhaveshp90 — Tue, 04 Dec 2018 17:49:45 GMT

@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

Re: How to detach in Set analysis

sunny_talwar — Tue, 04 Dec 2018 17:55:06 GMT

Just combined the set analysis from your other two expressions (set analysis on sanpshot_date) and added it to the if clause expression.

Re: How to detach in Set analysis

bhaveshp90 — Tue, 04 Dec 2018 17:59:48 GMT

Awesome thank you sir