topic Re: Combined set analysis in QlikView

Combined set analysis

Hoowling — Sat, 16 Nov 2024 01:35:25 GMT

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

Re: Combined set analysis

Anil_Babu_Samineni — Mon, 06 Jan 2020 11:44:44 GMT

What about this?

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

Re: Combined set analysis

Hoowling — Mon, 06 Jan 2020 11:53:15 GMT

Thanks for quick response.

No, that expression returns zero value....

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

//Hoowling

Re: Combined set analysis

Anil_Babu_Samineni — Mon, 06 Jan 2020 12:03:46 GMT

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

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

Re: Combined set analysis

Hoowling — Mon, 06 Jan 2020 12:36:47 GMT

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

Re: Combined set analysis

Sue_Macaluso — Mon, 06 Jan 2020 12:42:59 GMT

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

Re: Combined set analysis

Hoowling — Mon, 06 Jan 2020 12:46:07 GMT

I'm using Qlikview

Re: Combined set analysis

Anil_Babu_Samineni — Mon, 06 Jan 2020 13:41:18 GMT

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

Re: Combined set analysis

kb_yan — Mon, 06 Jan 2020 14:04:21 GMT

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)

Re: Combined set analysis

Brett_Bleess — Mon, 06 Jan 2020 17:01:29 GMT

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/SetAnalysis/set-analysis-expressions.htm

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

Re: Combined set analysis

sunny_talwar — Mon, 27 Jan 2020 17:37:37 GMT

How about this?

Sum({[P1] * $<$(sIgnoreFieldsWF), [FC_StoreDate] ={'$(=vMaxDateFC)'}>} [FC_HistoricalForecast])