Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
For my application I tried several things to make a Current month calculation. The problem is that customer would like to select a year/month combination for 1 expression and another expression always shows the current month figures.
I tried several thins to achieve this:
1. make Current month field in script
2. make 'CM' indicator field in script
3. expression: Sum({$<Year_Month={$(=Year(today())&num(month(today()),'00'))}>}Score)
All these options work perfect as long as I select the current month. But I do not want to select the current month. I want to select for example July 2010 and 1 expression is showing this while another expression always shows the current month figure; in this case december 2010.
How can i change above expression to achieve this?
Thanx a lot for helping me!
Best regards,
Patricia
Patricia,
Take a look at the Dashboard tab of the What's New in QlikView 9 application (http://community.qlik.com/media/p/67011.aspx). You will see that to show year comparisons, the example uses max(year) and max (year)-1.
This should point you in the right direction.
Matt
Yes, I saw this example. It is a nice example which shows you the max year if no selections are made. But if you choose for 2007, the current year figure will change to 2007. I already used this calculation for my application.
What i would like, that if I select 2007, the current year of 2009 will still be shown. Another expression will show the figure of 2007.
Any idea?
Ok, it sounds to me like you have separate Year and Month fields that you are displaying in listboxes. Your expression number 3 is almost correct for the current year, ie. Sum({$<Year_Month={$(=Year(today())&num(month(today()),'00'))}>} Score) but you need to tell QlikView to ignore any selections in your separate Year and Month fields. To do this add 2 more conditions to your set analysis expression, i.e. Year=, Month=.
Sum({$<Year_Month={$(=Year(today())&num(month(today()),'00')), Year=, Month=}>} Score)
Hope this helps,
Matt
Oops - missings '}'. Should be:
Sum({$<Year_Month={$(=Year(today())&num(month(today()),'00'))}, Year=, Month=}>} Score)
Thank you for your reply.
I tried your solution (and more...). The calculation works perfect as long as you have the current month selected....
Hi Patricia
I use this and it´s working fine.
=Sum({$<Year={$(=Year(today()))},Month={$(=Month(today()))}>} Order)
Regards
Anders
Hi Anders!
Great, it works! Don't know what went wrong. I think the num function was not necessary, but i thought I tried without and that didn't work. Very strange...but I am happy with the result.
Thank you both a lot, you saved me!