topic Re: Conditional SUM in QlikView

Conditional SUM

Fri, 06 Sep 2013 22:18:17 GMT

Good Evening,

I'm, trying to do something like that:

=Sum(if(MovementType='Sales' and Year(Date)=Max(Year(Date)),Sales))

MovementType = it's to identify the type of Fact

I want to Sum the sales of the current year in the selection and the previous year.

Can you show me why this is not working but the expression says ok?

Best Regards,

Ricardo Perfeito

Re: Conditional SUM

nicolett_yuri — Fri, 06 Sep 2013 22:40:45 GMT

Try it,

Sum({< MovementType= {"Sales"}, Year={'$(=Max(Year(Date))'}>} Sales)

Create a column with a Year of date.

Re: Conditional SUM

jduenyas — Fri, 06 Sep 2013 23:10:39 GMT

The reply by Yuri as correct.

Here though is the explanation:

Your expression is correct (there are no gramatical errors in it) so it wil say that the expression is OK.

However, you are not selecting a Year you are only comparing one function's value to another function's value

Year(Date)=Max(Year(Date))

It should be Year =Max(Year(Date)), where Year is a selectable column.

your table should look something like this:

MovementType	Year	Sales
Sales	2012	100
Purchase	2012	200
Purchase	2013	150
Sales	2013	250
Sales	2013	300
Purchase	2013	450

Re: Conditional SUM

Sat, 07 Sep 2013 07:41:11 GMT

Thank you all for showing me the way!!!.

Yuri:

Your expression is missing one )

Sum({< MovementType= {"Sales"}, Year={'$(=Max(Year(Date)))'}>} Sales)

However i made a different approach and i want to share with you.

I have a master calendar, so i don't need to add a column in my fact table. The Year is a column that i have in my master calendar table. I link the fact table through the column Date to the master calendar.

What do you think about this solution?

Awesome forum!!!

Best Regards,

Ricardo Perfeito