how to use P() function for latest date only

[tauceef9]

Hi All,

I have a KPI called Not-Installed Patches, where in I am counting all the Patches which are not installed in any of the computers. A Patch can be linked with multiple computers and if it is not installed in any single computer as well it will be counted as Not-Installed.

I am using below set analysis for the same:

count( {$<Patch_SNO = P({$<[UpdateStatus]={'NotInstalled','Downloaded'}>}Patch_SNO),[Report Date Num]={'$(vMaxCalendarDate)'}>}distinct Patch_SNO)

It works when we don't select anything from the filters Just noticed, it is not working even if I don't select anything.

Thing is like this set analysis is considering entire data set (from database) instead of current selection. but when I select a filter let's say a "Computer" which is all Patched but one of the Patch is displaying as Not-Installed.

When I am selecting any Computer from the filters it is showing all those Patches as not installed which are actually not installed (may be once in the history or for other computers) in the database.

In actual this Patch is installed on the selected computer but it is not installed in another computer, and this Patch is coming as Not-Installed for the selected and all the computers linked to this Patch. It means the above P() function in considering all the computers instead of current selected computer.

I have already applied filter to pick the data for latest date

Report Date Num]={'$(vMaxCalendarDate)'}

still it is considering history dates as well.

I want to apply the above set analysis for the current selection only and for the latest date.

Please help me with this.

Regards,

Tauceef

Message was edited by: Tauceef Sheikh

Message was edited by: Tauceef Sheikh

Message was edited by: Tauceef Sheikh

[tauceef9]

Now I understood more.

It is working for current selection only but the thing is I am not able to get the data for latest date.

Even after applying filter of

Report Date Num]={'$(vMaxCalendarDate)'

It is referring the complete data set (Historical records).

Please let me know how can I apply filter of latest date with P() or E() functions.

To explain it better, I am trying to calculate the count of compliance/non-compliance computers as well as compliance/non-compliance patches.

On a day one computer can have multiple patches installed, similarly a patch can be installed on multiple computers.

If a computer doesn't have all the patches approved installed will be considered as non-compliance.

and if a patch is not installed on all the associated computers will be considered as non-compliance.

Due to many to many relationship I can't use = operator like below:

Compliance Computers = Count({$<[UpdateStatus]-={'NotInstalled','Downloaded'},[Report Date Num]={'$(vMaxCalendarDate)'}>} Distinct ComputerID)

Non-Compliance Computers = Count({$<[UpdateStatus]={'NotInstalled','Downloaded'},[Report Date Num]={'$(vMaxCalendarDate)'}>} Distinct ComputerID)

because if out of multiple patches if a single patch is installed that computer will get counted in both compliance and non-compliance numbers that is why I am using E() and P() functions but that is not working for the latest date filter.

Hope this will explain my problem better, would appreciate any help. Thanks.

Regards,

Tauceef

[shraddha_g]

try [Report Date Num]={"$(=vMaxCalendarDate)"}

also share what you have used while defining variable vMaxCalendarDate

[tauceef9]

Hi Shraddha,

Thanks for your response.

It gives the same result with the above change. I have tried to give this date condition inside P() & E() functions but that also doesn't seems to be working.

Below is the definition of MaxCalendar variable:

=Max([Report Date Num])

[Report Date Num] is the "Last modified date" converted into number.

Regards,

Tauceef

[sunny_talwar]

It might help to see few rows of data to understand the real problem here... I have some idea what the issue might be, but without seeing some raw data, it might not be very simple to get you what you are looking to get. Would you be able to share some sample data?

[tauceef9]

Hi Sunny,

Below is the data model I have in place

TargetGroupMaster Contains all the Group related details.

ComputerMaster Contains all the computers. (_key_CompStatus is the combination of ComputerSNO and Date). SerialNumber gets generated on a daily basis.

AllPatch contains all the Patches.

And the center table ComputerPatchStatus contains all the Patches applied to all the computers with their status and date. This table contains history of patches applied to the computers.

Both ComputerMaster and PatchStatus are daily incremental load, that's why I have combined ComputerSNO and Date to link both the tables.

Sample data attached for your reference. Same color of rows represents the same Computer for different dates.

DataModel image contains my original database tables, I have combined ComputerTargetGroup, ApprovedPatchGroup and ComputerWiseUpdateStatus into one center table.

Please refer below thread if need more details:

How to fix circular loop in the data model

Regards,

Tauceef

[sunny_talwar]

Thanks for the elaborating. What exactly are you going to be counting here? I guess I don't really see ComputerID here... so I wonder what is that you are trying to count. Also, it would be helpful to know what is the expected output here?

[tauceef9]

I am trying to get the Compliance % of both Computers and Patches.

On a particular day what is the Compliance % of Computers and Patches.

Computer Compliance % = Number of Computers where UpdateStatus Not in( 'NotInstalled','Downloaded')/ Total Computers.

Computer No-Compliance % = Number of Computers where UpdateStatus in( 'NotInstalled','Downloaded')/ Total Computers.

**That UpdateStatus should be for all the Pathces related to that computer on a day.

Patch Compliance % = Number of Patches where UpdateStatus not in('NotInstalled','Downloaded')/ Total Patches.

Patch Non-Compliance % = Number of Patches where UpdateStatus in('NotInstalled','Downloaded')/ Total Patches.

**UpdateStatus should be for all the Computers related to this Patch on a day.

By default it should show the compliance as of last day but when user selects any date or month then Compliance % on that day or the last day of the selected month.

ComputerID is there in the ComputerMaster table.

Regards,

Tauceef

[pradosh_thakur]

try this  in the variable expression

=Max({1}[Report Date Num]) it eill give you the latest date irrespective of the selection if this is what you want

regards

Pradosh

[tauceef9]

Is there a way we can write nested P() & E() functions?

Inside we will use P() which will return all the rows for the max date

ComputerID=P(<[Report Date Num]={'$(vMaxCalendarDate)'}>)

and outer E() function will exclude all the rows from the selected rows for UpdateStatus not in('NotInstalled','Downloaded')

ComputerID=E(<[UpdateStatus]={'NotInstalled','Downloaded'}>)

Just a thought, not sure if this is possible.

Regards,

Tauceef