Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Have a gauge chart on my dashboard that does the following calculation:
sum({<[Ledger Type]={'BA'}
,Source={'F0902'}
,[JDE.Year] = {$(=$(vV_CurrentYear))}
>}Amount/100)
vV_CurrentYear = Year(Today()) so the chart is currently defaults to the current year.
However, we'd also like to enable the users to select other year from the 'Year' filter pane. Please note, they can only select one single year from the filter but not multiple years.
Please let me know how to achieve this via set analysis.
Thanks in Advance.
Get rid of the min().
num(sum({<[Ledger Type]={'AA'}
,Source={'F0911'}
,[JDE.Year] = {$(=RangeMin([JDE.Year],Year(today())))}
// ,[JDE.Year] = {$(=max([JDE.Year]))}
>}Amount/100),'#,##0')
Yes, it's a bit if a trick that it works. What you really want is the max JDE.Year but no later than today. When nothing or more than 1 JDE.Year is selected, [JDE.Year] is returning null because there is only one value. So it uses today().
It will however break if more than one JDE.Year is selected. It actually should have a max() in there:
,[JDE.Year] = {$(=RangeMin(max([JDE.Year]),Year(today())))}
-Rob
[JDE.Year] = {$(=max(Year))}
-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com
Hello,
You can use this calculation to achieve this considering your fie;d name is Year
= sum({<[Ledger Type]={'BA'}
,Source={'F0902'}
,[JDE.Year] = {$(=GetFieldSelections(Year))}
>}Amount/100)
Output would be like this
[JDE.Year] = {$(=GetFieldSelections(Year))}
Would this give different results than not specifying JDE.Year at all?
-Rob
Rob,
[JDE.Year] = {$(=max(Year))} works great and it shows the data for the year I selected too.
However, is there anyway I can still use {$(=$(vV_CurrentYear))} to load the initial data with current year data. Then once user select the year from the filter pane, it will overwrite the current year data.
sum({<[Ledger Type]={'BA'}
,Source={'F0902'}
,[JDE.Year] = {$(=$(vV_CurrentYear))}
// Any way to add [JDE.Year] = {$(=max(Year))} here to overwrite the initial load of current year data if user select another year?
>}Amount/100)
Isn't max(Year) equal to the current year?
-Rob
The thing is if I only use [JDE.Year] = {$(=max(Year))}, the dashboard shows 0 when it initially loads. Users need to select the year from the filter pane in order to show the data.
Basically I want the dashboard to initially load current year data based on {$(=$(vV_CurrentYear))} but user able to switch to other year based on {$(=max(Year))}. Does that make sense?
I think I'm confused as to the relationship between JDE.Year and Year. Are they linked? Or is Year in a data island?
Actually we only have 'JDE.Year' for list of years. There's no 'Year' field.
It works as follow:
[JDE.Year] = {$(=max(JDE.Year))}
Sorry for the confusion.