Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

cytd-lytd -question?

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

3 Replies
Highlighted
Specialist II
Specialist II

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.

 

Highlighted
Creator II
Creator II

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?

Highlighted
Specialist II
Specialist II

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:

YTD.PNG

 

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

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

 

YTD2.PNG