Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi community,
I have an expression that looks like this:
=Sum({[P1]*$<$(sIgnoreFieldsWF)>}[FC_HistoricalForecast])
The ignore fields variable is used to ignore selections in certain list boxes.
Now I also want to include in the set analysis that only values at max date should be summarized for FC_HistoricalForecast. (i.e. somthing like this: Sum({<[Date]={'$(=Max([Date])'}>} Value)
How do I combine this into the set analysis?
//Hoowling
What about this?
=Sum({[P1]*$<$(sIgnoreFieldsWF)>} If(Date=Max(TOTAL Date), [FC_HistoricalForecast]))
Thanks for quick response.
No, that expression returns zero value....
Isn't it possible to include the max date function within the brackets?
//Hoowling
I assume, You don't want that Alternate State to effect into Data. May be try
=Sum({[P1]*$<$(sIgnoreFieldsWF), Date={$(=Max(Date))}>} [FC_HistoricalForecast])
Hi,
That doesn't work either.
This expression gives the value at max date (not taking the alternate state in consideration):
Sum({<[FC_StoreDate]={'$(=vMaxDateFC)'}>} [FC_HistoricalForecast])
I've tried replacing the date section in your proposed solution with this expression, but doesn't get it to work.
Sum({[P1]*$<$(sIgnoreFieldsWF), Date={$(=Max(Date))}>} [FC_HistoricalForecast])
@Hoowling Are you using Qlik Sense or QlikView? I would like to move this into the correct product forum. Thanks.
I'm using Qlikview
Maximum date will consider alternate state since there is one identifier of open/close methodology. May be @sunny_talwar will help, Instead try this way for a while?
Sum({[P1]*$<$(sIgnoreFieldsWF), Date={$(=Max({[P1]*$<$(sIgnoreFieldsWF)>} Date))}>} [FC_HistoricalForecast])
Try using a "Field Event Trigger" (in Document Properties / Triggers) - when date field is changed (due to other selections) it changes to assign the value to a variable -
Create a variable vDate
The trigger has an Action of Set Variable
Variable name : vDate
Value =max(Date)
then you can use the variable in set analysis or if statement in expression
sum(if date>$(vDate), whatever)
The only things I can provide in addition to what Anil has been trying to help with are the following Design Blog posts:
https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344
There are some related posts at the bottom of that post, just FYI.
If you wish to further search the Design Blog area, use the following URL:
https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog
The only other thing I can offer is Help link too in order to check things there:
I also am fairly certain Sunny is taking some time off, so not sure if he will see things, just wanted to give a heads up on that too. Unfortunately I am not much actual technical help to you, apologies for that.
Regards,
Brett