Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
New Contributor II

Modifier as initial filter

 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

 

2 Solutions

Accepted Solutions
Highlighted
Partner
Partner

Re: Modifier

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

)

View solution in original post

Highlighted

Re: Modifier as initial filter

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

View solution in original post

10 Replies
Highlighted
Partner
Partner

Re: Modifier

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

)

View solution in original post

Highlighted
Digital Support
Digital Support

Re: Modifier as initial filter

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/...

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Highlighted
New Contributor II

Re: Modifier

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 

 

 

 

Highlighted
Partner
Partner

Re: Modifier

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

Highlighted

Re: Modifier as initial filter

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. 

Highlighted
New Contributor II

Re: Modifier as initial filter

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 

 

Highlighted
Digital Support
Digital Support

Re: Modifier as initial filter

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Highlighted

Re: Modifier as initial filter

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)

Highlighted
New Contributor II

Re: Modifier as initial filter

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) 

example.PNG

 

Thanks in advance 

Kostas