12 Replies Latest reply: Nov 29, 2017 8:36 AM by Sunny Talwar RSS

    how to use P() function for latest date only

    Tauceef Sheikh

      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

        • Re: how to use P() function for latest date only
          Tauceef Sheikh

          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

          • Re: how to use P() function for latest date only
            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?

              • Re: how to use P() function for latest date only
                Tauceef Sheikh

                Hi Sunny,

                 

                Below is the data model I have in placedatamodel.jpg

                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

                  • Re: how to use P() function for latest date only
                    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?

                      • Re: how to use P() function for latest date only
                        Tauceef Sheikh

                        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

                        • Re: how to use P() function for latest date only
                          Tauceef Sheikh

                          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

                          • Re: how to use P() function for latest date only
                            Tauceef Sheikh

                            I have tried many options but nothing seems to be working :

                            1.Giving Date filter inside E()

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

                            2.Using if() for date condition

                            =if([Report Date Num]="$(=vMaxCalendarDate)",Count({<ComputerID=E({<[UpdateStatus]={'NotInstalled','Downloaded'}>})>}Distinct ComputerID))

                            3. Using if() for date after set

                            =Count({<ComputerID=E({<[UpdateStatus]={'NotInstalled','Downloaded'}>})>} if([Report Date Num]="$(=vMaxCalendarDate)",Distinct ComputerID))

                             

                            I can see that E() or the P() functions always looks for entire dataset, isn't there a option to restrict them to a particular dates set or a date?

                             

                            Regards,

                            Tauceef