topic calculating an expression using only 1 reference in QlikView

calculating an expression using only 1 reference

Mon, 01 Mar 2010 14:28:26 GMT

Hi,

I hope i'm posting in the right forum. If I'm not, pls forgive me.

I have a set of data for counting absence of personel.

Of course it starts with the calculation of the hours personel can take vacation. This is already done in my database.

Now here's the problem. When I collect the data in Qlikview I have records looking like this:

date allowed last year used employeenr

01-01-2010 80 10 5 101

I have made a textbox where I want to calculate all the allowed hours. This is my formula:

num((sum(allowed)+sum(last year)),'#.##0,00')

This works great, untill the selection is not only a Year, but also a month.

How can I change this formula so it only looks at the selected Year? If the user makes the selection 2010 and (for example) march, it stil has to calculate the sum at the beginning of the year. It would totally be great if, when the user makes no selection, it calculates the Year out of todays date.

Since English is not my language, I hope you understand what I mean,

thanks in advance,

Yvonne

calculating an expression using only 1 reference

Mon, 01 Mar 2010 14:50:47 GMT

Hi there.

You can use set analysis to enable you to always choose a default year which in this case would be 2010. A set analysis expression solution to your text box could be for example:


NUM(Sum( {< Year={$(=(Year(Today()))}>} Allowed) + Sum ({{< Year={$(=(Year(Today()))}>} (Last year),'#.##0,00')

You could also code it such that the higher value of Year in your calendar is always chosen, assuming you have a calendar to go with your table


NUM(Sum( {< Year={$(=(MAX(Year)}>} Allowed) + Sum ({{< Year={$(=(MAX(Year)}>} (Last year),'#.##0,00')

Hope it helps you on your way.

calculating an expression using only 1 reference

Mon, 01 Mar 2010 15:07:44 GMT

Hi, thanks, it doesn't seem to work.

This is the expression.

=NUM(Sum( {< Jaar={$(=(Year(Today()))}>} toegestaan) + Sum ({{< Jaar={$(=(Year(Today()))}>} (Last year),'#.##0,00')

I suppose I must change your {Year={ into Jaar= because that is my selection (Year in Dutch = Jaar)

I changed Allowed into toegestaan, because that is my translation in Dutch and also the right name of de databasefield. Do I have a problem with Last year? Does Qlikview know what that means?

Thanks for trying to help me.

Yvonne