Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

calculating an expression using only 1 reference

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

2 Replies
Not applicable
Author

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.

Not applicable
Author

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