How disregard dimension selection works in set analysis for aggregation?

[Anonymous]

Hi,

I am calculating YTD value using the below set analysis

=Sum({<PeriodId = {"<= $(=Max(PeriodId))"},Year={"$(=Max(Year))"},Month=>}Sales)

By definition, ignoring a dimension means anything we selected in that dimension List Box will be ignored in the set analysis.

But here:

=Sum({<PeriodId = {"<= $(=Max(PeriodId))"},Year={"$(=Max(Year))"},Month=>}Sales) Works as expected. If I select a Month dimension in the list box it am getting the correct Year to Date value

Whereas if i use =Sum({<PeriodId = {"<= $(=Max(PeriodId))"},Year={"$(=Max(Year))"}>}Sales) by removing the "Month=", and select a Month dimension in the list box, it shows the result for that Month alone not the YTD value.

Could you please explain, how adding the "Month=" changes the value here?

Also, as per definition, if we use Dimension= in the set analysis, then that dimension selection should be ignored right? But in the above two different cases, when i select a Month the value changes. How Qlikview handles that? Please help with an explanation.

[swuehl]

Your Month selection influences the result of the dollar sign expansion:

$(=Max(PeriodId))

Remember that the dollar sign expansion already take place before the (set) expression gets parsed and evaluated.

Assuming for current Month, this might return something like 5 and with current year 2016, your two set expressions look like

=Sum({<PeriodId = {"<=5"},Year={"2016"},Month=>}Sales)

=Sum({<PeriodId = {"<=5"},Year={"2016"} >}Sales)

Since Month is ignored in the first case, the resulting set can return all PeriodId <=5 for year 2016, YtD.

Since Month is not ignored in the second case, the intersecting records of first case with selected month records is returned, i.e. current month.

Read more in

The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync

[sunny_talwar]

I responded to your original post as well

YTD Expression not working properly

[dadumas]

Stefan,

Unfortunately:

=Sum({<PeriodId = {"<= $(=Max(PeriodId))"},Year={"$(=Max(Year))"},Month=>}Sales)

while coded perfectly, does NOT ignore selections on Month.  It should, as "Month="  is supposed to ignore selections on Month.  It does not, and I consider this is a bug.  I have never found a workaround.

Dave

[dadumas]

I just found the work around from another post from Sunny Talwar.  I have not seen this anywhere in the docs, nor in any of the books - I have many.  This indeed works if you really really want the ignore selections on Month to work as a true YTD measure shoud work.  thanks Sunny!:

Sum({<PeriodId = {"<= $(=Max({<Month>}PeriodId))"},Year={"$(=Max({<Month>}Year))"},Month=>}Sales)

Adding the {<Month>} modifiers "fixes"  what I would call a bug in set analysis.  This should not be that hard.

[swuehl]

Hi David,

this is a pretty old thread. If you have a currently active issue, I would recommend opening a new thread.

Besides this, it's definitely not a bug what you see, but as Qlik would say 'working as designed'.

Note what I answered above at the very beginning:

"Your Month selection influences the result of the dollar sign expansion:

$(=Max(PeriodId))

Remember that the dollar sign expansion already take place before the (set) expression gets parsed and evaluated."

Hence, you indeed need to add the set expression also to your dollar sign expansion aggregations, if you want to ignore the dimension in the max period.

Note that similar also applies for expression searches in your set modifier.

[dadumas]

Right, but no one actually answered the issue with  the code syntax solution except for Sunny Talwar.  Anyway, if anyone sees this, they can use that syntax I outlined.  Thanks  Dave

[swuehl]

Well, I think the set analysis in the dollar sign expansion should not be used in the expression that the OP of this thread posted, given the fact that he wants to see the YTD value using the month selected (and that's what he confirmed when telling us that the expression works as he expected).

I guess you are looking for a different use case scenario and I don't know who answered to your request and who did not. That's why I asked you to create a new thread (if you still have an open issue) and describe your requirements therein, not mixing up this topic with yours.

But I am pretty sure that Sunny answered your request correctly. No doubt.

Regards,

Stefan