how to do the static dates into dynamic in set analysis or load editor

[Sara_3]

Hi,

The following are set analysis i have used in the KPI but i need to present the data in simple table:

i have the actual data from 2019 - 2023 but the following are the set analysis of Month Jan 2023:

1- Count({$<[P Release Date]={"<=1/1/2023"}, [Del On Flag]={N},[PO LI RelDT Flag]={N}>}[P Release Date])

2- Count({$<[P Release Date]={"<=12/31/2022"}, [Del On Flag]={N},[PO Release Date]={">=1/2/2023"}>}[P Release Date])

3- Count({$<[P Release Date]={"<=1/1/2023"}, [Deleted On]={">=1/1/2023"},[PO LI RelDT Flag]={"N"}>}[P Release Date])

4- Count({$<[P Release Date]={"<=1/1/2023"}, [Deleted On]={">=1/1/2023"},[PO Release Date]={">=1/1/2023"}>}[P Release Date])

Main Data table:

Load
if(date(Date#("Deleted On", 'MM/DD/YYYY')) > 0 ,'Y','N') as "Del On Flag",
if(date(Date#("PO Release Date",'MM/DD/YYYY')) > 0 ,'Y','N') as "PO LI RelDT Flag",

Date(Date#([P Release Date], 'M/D/YYYY h:mm'), 'MM/DD/YYYY') as [P Release Date],
Date(Date#([Deleted On], 'MM/DD/YYYY')) as [Deleted On],
Date(Date#([PO Release Date], 'MM/DD/YYYY')) as [PO Release Date],
monthstart((Date#([PO Release Date], 'MM/DD/YYYY'))) as [MonthStart]

from excel sheet;

Thanks,

Sara.

[rubenmarin]

Hi, what yo meant for dynamic? based on user selections? and in that case... what are the conditions for each date?

In example, in you want to use the min date selected it could be done as:

Count({$<[P Release Date]={"<=$(=Date(Min([DateField])))"},...

So $(= will evalute the expression and return the value to the set analysis.

This is called $-Expansion and is calculated before the table, so it doesn't uses dimension values, a table with year as dimension will return the same value for all rows, not applying the year of the row, only the selcctions can change this values.