nested set analysis

[hov]

Hi, I am new to qlik and am having some trouble with a set analysis expression im trying to handle.

I am trying to calculate the diff of a measure between the max date in the selection and its predecessor. 

date_num = integer value of my date, increasing

report name = report selection 

A region filter also applies 

I would like something as follows however this doesnt work when a single date is selected. 

count({<date_num={$(=max(date_num))}>}ReportName) - count({<date_num={$(=max(date_num, 2))}>}ReportName)

I would like to keep the other two filters to work as normal (reportname and region)

[agigliotti]

Hi @hov ,

What's the expected result when a single date has been selected?

[MayilVahanan]

Hi

If the dates are continuous, then try like below

count({<date_num={$(=max(date_num))}>}ReportName) - count({<date_num={$(=max(date_num)-1)}>}ReportName)

[hov]

Hi, the expected result would be the diff of the measure to the date selected from the previous date available considering the rest of the current selections.

So, my problem is I can't figure how to work the set to retrieve previous dates when one is selected. When both dates are selected it works fine.

[MayilVahanan]

Hi

Hope below will suit for your requirement

count({<date_num={$(=(Alt(Max(date_num,2),Max(date_num)-1)))}>}ReportName)

[hov]

The issue is that when one date is selected the second to max date is not available. Need to figure a way to bypass this. 

[marcus_sommer]

If you select a single date you creates a single max. date - there is no second value existing because all other dates aren't available respectively possible anymore. This restriction might be bypassed with an own set analysis - for example by ignoring the selection - but depending on the dataset it may not always return the wanted value else it could become a bit difficult to specify the conditions.

Probably easier would be you applies the provided logic of:

max. Date - 1

and if there are no continuous dates (not existing or not relevant in regard to weekends or holidays) you could create such continuing date within the master-calendar with respect to all the workdays-stuff.

- Marcus

[hov]

Do you think something like this would be possible? for the second expression to use {1} together with all the conditions inside the brackets. The syntax below im not sure about but could it work?

count({<date_num={$(=max(date_num))}>}ReportName) - count({1<date_num={$(=max(date_num, 2))}, ReportName={$(GetFieldSelections(ReportName))}, Region={$(GetFieldSelections(Region))}>}ReportName)

[marcus_sommer]

In general, you could with {1} or something like: {< Field = >} ignore the selection state of the application respectively of a certain field - whereby my hints were aimed not the main-calculation else just to the condition like: {$(=max({< datenum >} date_num, 2))} - but it's not a general solution else moving the challenges to another part and often adding more complexity as needed.

Therefore, I would use the mentioned max. date - n logic. At first it may look like more efforts but to have all possible period-fields including the entire workingday-stuff and offset-values, flags and so on within a master-calendar is a tremendous simplification for nearly all kind of views within all applications and will later saving a lot of work and time.

- Marcus

[hov]

Hi thanks for your answer.

Im not sure how date - n logic will bypass my selection, it seems it will produce the same issue.