Set analysis help

[mahitham]

Hi,

Can any one please help me on below scenario.

I have the Source Data like below

1. Here From Date Field need to create [Report Date] and [Comparision Date] fields like below

[Table]:
LOAD
    "Date"    as   "Report Date",
    ID,
    Name
FROM [lib://App/Add,Remove,Replace.xlsx]
(ooxml, embedded labels, table is Sheet1);

[Alt Table]:
Load
    "Report Date"    as   "Comparision Date"
Resident Table;

2. Now the UI looks like below with Two Filters.

From Report Date need to take only Max date i.e., 13/10/2019

The Requirement is need to compare Max date with other dates data and need to derive how many Names added , Removed and Renamed.

For example when we compare 13/10/2019 with 12/10/2019 , 3 Names added, Name= C deleted, Name = E renamed with M. These Added,Deleted and Renamed count has to show on KPIs and Tables. When user selects 11/10/2019 from comparison date then need to compare  13/10/2019 name with 11/10/2019 names.

I have derived Added count based on below formula

COUNT({$<[Report Date]=P([Report Date])>}DISTINCT Name)
-
COUNT({$<[Report Date]=P([Comparison Date])>}DISTINCT Name)

but I am not able to derive Renamed and Deleted count.Please help me on this.

Please find the below attached excel source.

Thanks in advance.

[Channa]

why you have ADD for 6F

it is available in both 12,13

[sunny_talwar · Accepted solution]

Try these

Added

=Count(DISTINCT {<ID = E({<[Report Date] = P([Comparision Date])>})>} ID)

Deleted

=Count(DISTINCT {<ID = E(ID), [Report Date] = p([Comparision Date])>} ID)

Renamed

=Count(DISTINCT {<ID = {"=Name <> Only({<[Report Date] = p([Comparision Date])>} Name) and Len(Trim(Name)) > 0 and Len(Trim(Only({<[Report Date] = p([Comparision Date])>} Name))) > 0"}>} ID)

[Channa]

delete=Count(DISTINCT {<ID = P({[Group1]}ID)-P({[Group2]}ID)>} ID)

ADDED=Count(DISTINCT {<ID = P({[Group2]}ID)-P({[Group1]}ID)>} ID)

renames=IF(only( {<ID = p({[Group1]}ID)>} ID)=only( {<ID = p({[Group2]}ID)>} ID) and
only( {<Name = p({[Group1]}Name)>} Name)<>only( {<Name = p({[Group2]}Name)>} Name) ,Name)

Rename Count:=Count(distinct {<ID= {"=only( {<ID = p({[Group1]}ID)>} ID)=only( {<ID = p({[Group2]}ID)>} ID) and only( {<Name = p({[Group1]}Name)>} Name)<>only( {<Name = p({[Group2]}Name)>} Name) "}>}ID)

this attach App will help with deleted based one alternate state

[mahitham]

Hi @sunny_talwar 

Thanks for your reply.

For Renamed KPI, In a Table I have to show the Original Name and Renamed Name.

Could you please help me on how to get the original and Renamed Column Names.

[sunny_talwar · Accepted solution]

May be this

Original

=Only({<ID = {"=Name <> Only({<[Report Date] = p([Comparision Date])>} Name) and Len(Trim(Name)) > 0 and Len(Trim(Only({<[Report Date] = p([Comparision Date])>} Name))) > 0"}, [Report Date] = p([Comparision Date])>} Name)

Renamed

=Only({<ID = {"=Name <> Only({<[Report Date] = p([Comparision Date])>} Name) and Len(Trim(Name)) > 0 and Len(Trim(Only({<[Report Date] = p([Comparision Date])>} Name))) > 0"}>} Name)

[mahitham]

It's working great...Thanks a lot for your continuous  help @sunny_talwar 

[mahitham]

Hi @sunny_talwar 

Can you please help me on below issue.

The below expressions showing default results correctly.

I have a Status field with Draft, Pass,Fail values. When I apply this filter the Values are changing wrong.

For example "Deleted" KPI Showing Count as 2 one ID is Pass and another ID status is Fail.

When I filter on Pass instead of 1 its showing 22.

Could you please help me to apply status filter on below expressions.

Thanks in advance.

Deleted

=Count(DISTINCT {<ID = E(ID), [Report Date] = p([Comparision Date])>} ID)

Renamed

=Count(DISTINCT {<ID = {"=Name <> Only({<[Report Date] = p([Comparision Date])>} Name) and Len(Trim(Name)) > 0 and Len(Trim(Only({<[Report Date] = p([Comparision Date])>} Name))) > 0"}>} ID)

[sunny_talwar]

I am not sure I understand the issue? Can you may be create a sample to show the issue?

[mahitham]

Hi @sunny_talwar 

Thanks for your reply.

Please find the below attached application and Source File

The below screen shot results showing correctly by selecting Report Date and Comparison Date

But if  I filtered ID=6 in Added Table then the values in Removed and Renamed is changing. Here not able to apply any other filters. After Filtering ID=6 Removed count is changed from 1 to 5.

Please help to apply the ID and Name filters in the Expressions. I have to show all kpis results in the below table with other filters as well

Thanks in advance