topic Re: Set Analysis - Incorporating a Formula with a Variable in App Development

Set Analysis - Incorporating a Formula with a Variable

matthew_morge — Sat, 16 Nov 2024 02:35:38 GMT

Hi Guys,

I am trying to write a set analysis incorporating a formula with a variable, as follows:

Sum({$<[Customerorders MS Set Description]

={$( =WEEKDAY(NUM([Customerorders Wanted Delivery Date])-'$(vDayOffset)') )}>}

[Customerorders Forecast Qty])

Essentially, [Customerorders MS Set Description] contains weekday values, example 'Sun' through 'Sat'.

I am calculating back from [Customerorders Wanted Delivery Date] using a variable enumerating to a value of 1 - 5.

This is consistently returning zero values.

I know there are missing "..." within the expression above, what is actually tripping me up is the variable, it's quotation marks '' and dollar expansion.

I would be really grateful for any thoughts.

Many thanks,

Matt

Re: Set Analysis - Incorporating a Formula with a Variable

Gysbert_Wassenaar — Sat, 25 Apr 2020 11:21:29 GMT

My guess is you're trying to do what I've described in this post: https://community.qlik.com/t5/QlikView-Documents/set-analysis-intra-record-qvw/tac-p/1479691. The .qvw file won't be very useful if you're using QlikSense. Hopefully the screenshot below (also attached as a file) is readable enough.

Re: Set Analysis - Incorporating a Formula with a Variable

robert99 — Sun, 26 Apr 2020 00:39:25 GMT

Hi @Gysbert_Wassenaar

Wouldn't it be better (safer) just to not use set analysis and use if in a situation like this (Or set up in script)

As set out in this thread that Henric gave the correct solution to

https://community.qlik.com/t5/Qlik-Sense-App-Development/P-and-E-Syntax-issue/td-p/106074

Further, the search

count([Class Number]={"=[Claims Opened Date] = [Claims Reported Date]"}>[Claims Number])

will not always work either: Here, a hypercube with [Class Number] as dimension will be created in the background, and

[Claims Opened Date] = [Claims Reported Date]

will be used as measure. Records with this evaluated as TRUE will be included in the search result. But classes with multiple open dates and multiple report dates will be evaluated as

NULL = NULL

which in turn is FALSE, even if there are individual dates that match. So, the search will not (always) work either.

The solution with the If() function will work fine, though.

HIC

Re: Set Analysis - Incorporating a Formula with a Variable

matthew_morge — Sun, 26 Apr 2020 10:26:21 GMT

Thanks both for your input. Whilst enlightening responses I think my issue was much more basic and purely the result of the string not being properly expanded under the set analysis.

I have managed to get around this by doing a simple IF(MATCH formula in a dimension with the same criteria and it seems to be sufficiently doing the job.

Re: Set Analysis - Incorporating a Formula with a Variable

matthew_morge — Tue, 05 May 2020 14:14:12 GMT

Still struggling on this formula, I need to avoid IF statements where I can so I am still trying to get the set analysis to work. The issue I think I am encountering is formula is not evaluating correctly.

The following is the expression:

=SUM({$<[Customerorders MS Set Description]={"$(=WEEKDAY(NUM([Customerorders Numerical Wanted Delivery Date]-$(=vDayOffset))"}>}[Customerorders Forecast Qty])

Whilst the variable; vDayOffset is correctly returning a value of '5', it is the **WEEKDAY(NUM([Customerorders Numerical Wanted Delivery Date]** that I believe is not returning the correct information.

I have attached a screenshot of the help bar at the bottom of the expression editor for reference. It may be that I am missing something small but obvious within the expression. Whilst I love working with Qlik Sense, I hate getting into these simple but puzzling problems.

If anyone is able to enlighten me I would be extremely grateful.

Thanks,

Matt

Re: Set Analysis - Incorporating a Formula with a Variable

robert99 — Wed, 06 May 2020 01:34:18 GMT

@matthew_morge

Are you missing two )) Sometimes the OK at the bottom is not correct when you use more complex set analysis

$(=WEEKDAY(NUM([Customerorders Numerical Wanted Delivery Date] )) -$(=vDayOffset))

"I hate getting into these simple but puzzling problems"

When this happens with me I simplify and / or try different options to find out for sure what the issue is

For example have you tried

num(weekday(

Removing the variable >> -$(=vDayOffset)) to see if it gives the correct result

Calculating the weekday number in script. With and without subtracting the 5 days

etc