Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
)
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
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
)
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
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
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
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
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.
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
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
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)
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