Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Current Year vs Previous Year with a filter

Hi,

I have a problem about set analysis.

In attached sample data.

I have a calendar with two years - 2014 and 2015.

I want to sum, for the selected year - 2015, an Amount for months with a flag = E -> sum({<Flag={'E'}>}Amount) ->OK!

For the previous year (respect to the selected one) I want to sum the Amount for the same months of the selected year - if they exist - disregarding the flag.

Thanks!

1 Solution

Accepted Solutions
maxgro
MVP
MVP

max year

sum({$ <YEAR={$(=max(YEAR))}, FLAG={E}>} AMMOUNT)

and max-1 year

sum({$ <YEAR={$(=max(YEAR)-1)}, MONTH=P({$ <YEAR={$(=max(YEAR))}, FLAG={E}>} MONTH)>} AMMOUNT)

View solution in original post

2 Replies
maxgro
MVP
MVP

max year

sum({$ <YEAR={$(=max(YEAR))}, FLAG={E}>} AMMOUNT)

and max-1 year

sum({$ <YEAR={$(=max(YEAR)-1)}, MONTH=P({$ <YEAR={$(=max(YEAR))}, FLAG={E}>} MONTH)>} AMMOUNT)

Anonymous
Not applicable
Author

Thanks, it's correct.

Can you explain me how to manage the situation in which there are more customers?

In attached new data with two records about Customer B.

In my example, Ammount of customer B must not be included in the sum baceuase he has not flag E for current year. So the results must be the same (97 and 44).

I would like the calculation takes into account the situation of single customer and not globally.

So I want to consider, for each customer:

- Current Year: ticket with flag =E;

- Prevoius Year: ticket of the same month of current year disregarding flag.


and then sum all available Ammount.


Thanks!