Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I’m struggling with setting up an expression to be used in a chart.
My dimension is Job_Group
My expression is sum(Duration_In_Minutes)
What I’m trying to achieve is that the expression display data only for the most recent Effective_Date (max Effective_Date?). The chart also needs to be set up so that it doesn’t restrict data based on any other filtering done within the rest of the document.
Any guidance would be greatly appreciated.
=Count({1<Date={"$(=Max(Date))"}>}Date)
=sum
({1<Effective_Date = {"$(=max(Effective_Date))"}>} Duration_In_Minutes)
'most recent Effective Date' in global / document context?
Then try maybe something like this as expression:
=sum({1<Effective_Date = {$(=max({1} Effective_Date))}>} Duration_In_Minutes)
Yes, Effective_Date is a field generated through the load script.
When I used
=sum({1<Effective_Date = {$(=max({1} Effective_Date))}>} Duration_In_Minutes)
as my expression it returned no data. I'll continue to take a look.
Hi,
Is Effective_Date a dimension in your chart? Then I'd try:
Sum(If(Effective_Date = Max(Effective_Date), Duration_In_Minutes))
Use it first with a few rows loaded just to check if it works.
Hope that helps.
Miguel
The dimension I'm using is Job_Group.
I think my expression in my chart needs to be something like:
sum
({1<Effective_Date = {max(Effective_Date)}>} Duration_In_Minutes)But I'm missing something with my syntax - something is wrong as I'm getting an error.
Hi,
In regards to syntax:
= {$(=max(Effective_Date))}
This will return a numeric value if Effective_Date has a dual date value (the usual in QlikView). Another option is
= {"$(=Date(max(Effective_Date)), 'YYYYMMDD')"}
should the date is in YYYYMMDD format.
Hope that helps.
Miguel
=Count({1<Date={"$(=Max(Date))"}>}Date)
=sum
({1<Effective_Date = {"$(=max(Effective_Date))"}>} Duration_In_Minutes)
Thanks Miguel and Nizamsha - very helpful!