cytd-lytd -question?

[ioannagr]

I have two questions regarding this expression i've written :

count({1}{< [status] = {'1','2','3'}, CurYTDFlag={1}, Year=, Month= >} distinct [ID])
- count({1}{<[[status] = {'1','2','3'},LastYTDFlag={1}, Year=, Month= >} distinct [ID])

1) Adding {1} should be ignoring all filter  selections, right?  This isn't happening - why?

2) If I didn't want it to ignore all filter selections, does this mean that selecting a certain date this should show the count from 1/1/2020 to (say) 02/03/2020 minues 1/1/2019 to 02/03/2019?

Thanking you in advance 

Ioanna

[jwjackso]

1)

I think the placement of the {1} is in the wrong location, Set Analysis 

count({1< [status] = {'1','2','3'}, CurYTDFlag={1}, Year=, Month= >} distinct [ID])
- count({1<[[status] = {'1','2','3'},LastYTDFlag={1}, Year=, Month= >} distinct [ID])

2) No.  If you did not ignore the filters and selected 02/03/2020, I would expect the first part of the expression to have only data for 02/03/2020 and the second part of the expression to be zero because the LastYTDFlag is not set to 1 for 02/03/2020.

[ioannagr]

Hi and thanks for your reply

1) i changed the placement like so, but still isn't static 😕

2) what should i write for this in order to work?

@jwjackso , the date field for which i want to ignore filter selections has the name "Date", should i put it in the set analysis? Maybe that would make it static?

[jwjackso]

Yes, Date would need to be in the expression.  Another way to handle YTD calculations is creating variables.  Using the data below:

Data:
Load *,
Year([Date]) as [Year],
Month([Date]) as [Month];
Load * Inline [
Dept,CurYTDFlag,Date,Amt
D101,1,06/01/2020,100
D101,1,05/01/2020,100
D101,0,01/01/2019,100
D102,1,04/01/2020,200
D102,0,04/01/2019,200
];

Let vCurrentYTD=Replace('[Date]={"<=@(=Date(Floor(Now()),"MM/DD/YYYY"))"},CurYTDFlag={1},[Year]=,[Month]=','@','$');
Let vPriorYTD=Replace('[Date]={"<=@(=Date(AddMonths(Floor(Now()),-12),"MM/DD/YYYY"))"},CurYTDFlag={0},[Year]=,[Month]=','@','$');

The Replace statement is changing the '@' to '$'.  Below are the variables in use:

In the expression editor, you can see how the vCurrentYTD and vPriorYTD variables in the expression are expanded:

=Sum({<$(vCurrentYTD)>}Amt) - Sum({<$(vPriorYTD)>}Amt)