topic Re: Modifier as initial filter in QlikView

Modifier as initial filter

justwinebar — Sat, 16 Nov 2024 19:55:48 GMT

Hi exprerts,

I have implemented a straight table and one of the expressions is the following

= Sum({ < Year={$(=Year(Today())-1)}, DayofYear={"<$(=DayNumberOfYear(Today()-2))"}>} Sales)

It calculates based on the current selection , Last Year to Date ( LYTD) sales.

However when I override the current selection of the dimension DayofYear ( e.g select a specific day of year ) the modifier overrides values of current selection

Is there any way to implement it ?

In my point of view , I would like to represent a set of data where modifier works as an initial filter and then current selection overrides it .

Thanks in advance

Kostas

Re: Modifier

dplr-rn — Wed, 09 Oct 2019 13:49:42 GMT

couple of options. final answer depends on your data and use case.

1- you could replace today() with Max(DateField) or Max(DayofYear)

2- If above option is not possible. declare the expression with an if condition based on selection on DayofYear i.e. if(GetSelectedCount(DayofYear)=0

,CurrentExpression

,ExpressionWithoutDayofYear in set analysis

)

Re: Modifier as initial filter

Brett_Bleess — Wed, 09 Oct 2019 14:15:39 GMT

Kostas, did Dilip's post help you get what you needed? If so, please be sure to circle back and use the Accept as Solution button on his post to give him credit and let others know that worked.

Other than that, about the best I have are the following links to try to help you find something that might work:

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

That one has some additional links to other posts in it as well, just FYI

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/SetAnalysis/set-analysis-expressions.htm

Sorry I do not have anything better for you, but hopefully it may help if you are still working upon things. Feel free to provide an update if you think someone may be able to still help with some additional details.

Regards,
Brett

Re: Modifier

justwinebar — Wed, 09 Oct 2019 14:27:31 GMT

Hi Dilip,

Thanks for the quick update. I followed the second approach and it worked. I ll mark it as solution .

However I would like to ask you , what if I have in my expression many modifiers then I would end up with a huge "if then else statement" .

I guess that it must be a "prettier" solution where selections of list boxes will filter the initial modified values by any expression .

Thanks again

Kostas

Re: Modifier

dplr-rn — Wed, 09 Oct 2019 14:33:43 GMT

set analysis mean value gets fixed to the one you give in it. (with some subtle tweaks possible)

if all you want is a default values you could use sheet level triggers and set the default ones.

Also want to add for set analysis in addition to bretts links.. also check out below document created by someone. its quite comprehensive

https://community.qlik.com/t5/QlikView-Documents/Set-Analysis-syntaxes-examples/ta-p/1491810

Re: Modifier as initial filter

sunny_talwar — Wed, 09 Oct 2019 14:52:12 GMT

Another option using if statement can be

= Sum({<Year = {$(=Year(Today())-1)}, DayofYear = {"<$(=If(GetSelectedCount(DayOfYear) = 0, DayNumberOfYear(Today()-2), DayOfYear))"}>} Sales)

So, basically instead of doing the if statement at the expression level, you can do it at the set modifier level.

Re: Modifier as initial filter

justwinebar — Thu, 10 Oct 2019 14:16:10 GMT

Hi Sunny ,

Although it seems a better approach of constructing the exrpession , the result is not the expected. The symbol of inequality (<) works the same in condition statements.

In my case I need the following pseudo code to be represented in your equation

if (GetSelectedCount(DayOfYear) = 0 then DayofYear less or equal to DayNumberOfYear(Today()-2)

else DayofYear equal to DayofYear

Thanks again

Kostas

Re: Modifier as initial filter

Brett_Bleess — Thu, 10 Oct 2019 14:27:56 GMT

Kostas, any chance you can attach an example QVW file, I think in this case it may help you get what you want more quickly, as folks will be able to see everything they need in order to try to figure out why it is not working correctly.

Regards,
Brett

Re: Modifier as initial filter

sunny_talwar — Thu, 10 Oct 2019 14:38:35 GMT

Isn't this what it is doing?

=If(GetSelectedCount(DayOfYear) = 0, DayNumberOfYear(Today()-2), DayOfYear)

If nothing is selected use DayNumberOfYear(Today()-2), else use the DayOfYear selected

and we already have less than sign here....

=Sum({<Year = {$(=Year(Today())-1)}, DayofYear = {"<$(=If(GetSelectedCount(DayOfYear) = 0, DayNumberOfYear(Today()-2), DayOfYear))"}>} Sales)

Re: Modifier as initial filter

justwinebar — Fri, 11 Oct 2019 08:14:44 GMT

Sorry for being late in answering you, based on my knowledge DayofYear will be always less than whatever is selected or not .

If nothing is selected use DayNumberOfYear(Today()-2), else use the DayOfYear selected and we already have less than sign here.... =Sum({<Year = {$(=Year(Today())-1)}, DayofYear = {"<$(=If(GetSelectedCount(DayOfYear) = 0, DayNumberOfYear(Today()-2), DayOfYear))"}>} Sales)

I have attached an example (I have translated the major components in english), and I have placed a button "Yesterday" and then both solutions (Sunny's and Dilip's)

Thanks in advance

Kostas

Re: Modifier as initial filter

sunny_talwar — Fri, 11 Oct 2019 10:58:32 GMT

There were few issues

1) Incorrect Parenthesis within the expression... might have been my mistake

2) The field name is DayofYear, but we were using DayOfYear

3) The selection is taking place in the alternate state

Try this expression

= Sum({<Year = {$(=Year(Today())-1)}, DayofYear = {"<$(=if(GetSelectedCount(DayofYear, ',', 100000, 'Main')= 0, DayNumberOfYear(Today()-1), DayofYear))"}>} AfterOrderDiscountValue)

But remember this will only work if you directly make a selection in DayofYear field.... if you hope to make selection in other fields like day of month or day of week, then you might have change the expression based on the output you are looking to get