Combined set analysis

[Hoowling]

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

[Anil_Babu_Samineni]

What about this?

=Sum({[P1]*$<$(sIgnoreFieldsWF)>} If(Date=Max(TOTAL Date), [FC_HistoricalForecast]))

[Hoowling]

Thanks for quick response. 

No, that expression returns zero value.... 

Isn't it possible to include the max date function within the brackets? 

//Hoowling

[Anil_Babu_Samineni]

I assume, You don't want that Alternate State to effect into Data. May be try

=Sum({[P1]*$<$(sIgnoreFieldsWF), Date={$(=Max(Date))}>} [FC_HistoricalForecast])

[Hoowling]

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])

[Sue_Macaluso]

@Hoowling  Are you using Qlik Sense or QlikView?  I would like to move this into the correct product forum. Thanks.

[Hoowling]

I'm using Qlikview

[Anil_Babu_Samineni]

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])

[kb_yan]

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)

[Brett_Bleess]

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:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/...

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