Set analysis based on date selection

[Anonymous]

Hello,

I want to do calculations based on a date selection and I cant figure it out how to write the set analysis

I want to calculate the sum of PickQty based on the date selection in the master calendar.

%DateKey is equal to ExpectedShipDate due to the point of view (what is due on the selected date)

Basically I want to see: (assuming selection on %DateKey is today)

1. what have I done in the past that was actually due today

2. what have I done today that was due today

3. what have I done today that was due in the future

for example:

1. sum(PickQty) where PickEvent = WORKLOAD and "PickDate smaller than selected %Datekey"

2. sum(PickQty) where PickEvent = WORKLOAD and "PickDate equal to selected %Datekey"

3. sum(PickQty) where PickEvent = WORKLOAD and "ExpectedShipDate larger than selected %Datekey and PickDate equal to selected %Datekey"

One issue is the date format.

%DateKey is a regular date format but the others, PickDate and ExpectedShipDate are YYYYMMDD

I tried using a variable to work around but this makes the syntax in the set analysis even harder for me.

any help is apreciated.

attached a sample QVW with what I have tried so far.

Lucas

[Anonymous]

I think that won't work with set analysis, because you have to compare row by row...

Set Analysis are calculated once, outside the dimension hierarchy of your chart.

Also expression search won't work.  ( (sum({<Date1={"=Date1<Date2"}>},Value) )

So I think you'll have to create expressions like that:

sum(if(Date1<Date2),Value)

You could also use flags in the script but then you have to join the table.

[Anonymous]

I'm slightly confused by your example, but does this get you what you need?

[Anonymous]

I thought about using flags but I dont know why it should not work with the set analysis. You can do the set analysis based on something like '=GetFieldSelections()'

My workaround with a variable seems to work as well: PickDate={'<$(vShipDate)'}  (where vShipDate is defined with an =only(EDCDate) to get the suitable date format

only when placing TWO conditions for the third workload the approach does not work

sum({<PickEvent={'WORKLOAD'},PickDate={'$(vShipDate)'},ExpectedShipDate={'>$(vShipDate)'}>}PickQty)

Just found out that the above formula works when selecting ExpectedShipDate directly. Only when selecting the date via the %DateKey in the MasterCalendar the calculation does not work.

[Anonymous]

I think you got to the same point as I did. the first two calculations with one condition work but the thirst with two conditions fails.

[Anonymous]

What would you expect to see?  Can you mock it up? 

[Anonymous]

Hi Wollo,

@

I think I got to work what I wanted to see initially.

attached you see what I wanted to see. Volumes split into the three buckets depending on the selected date.

BUT, I found that the selection of the %DateKey in the MasterCalendar was the problem. When Im selecting the ExpectedShipDate directly it works perfectly.

%DateKey is just the duplication of the ExpectedShipDate so I dont get why it does not work. Especially since my Set Analysis works with a vShipDate which is the same independent of selecting %DateKey or directly ExpectedShipDate.

[Peter_Cammaert · Accepted solution]

Two suggestions (to get it to work):

• Change your vShipDate variable formula from =Only(EDCDate) to =Max(EDCDate). That way it will still work when multiple values are active in EDCDate.
• Add the following field reset modifier to the set analysis expression in both vWLPrepFut and vPRPrepFut:
    ... %DateKey=, ...
  Otherwise active selections in %DateKey will block the set analysis modifier that tries to set ExpectedShipDate to a range of dates.

Best,

Peter

[Anonymous]

Well, it works!

But tbh I dont understand what ", %DateKey=, " does in the Set Analysis

Why would my selection in %DateKey block set ExpectedShipDate

I mean 23.05.2017 as %DateKey sets ExpectedShipDate to 20170523 ?!

I would appreciate further explanation!

Thanks anyways!

[Peter_Cammaert]

You can easily find out what I mean by playing with just the selection fields in your master calendar. Imagine that you have a set expression that modifies the regular calendar selection to always include all data from the first of the year. If my only selection will be in an EDCDate listbox, and your set analysis will reset EDCDate to include everything from Jan 1st to the maximum EDCDate, then all will be ok. But if I dare making a selection in another listbox with for example field EDCMonth, my entire set analysis breaks down. Why? Because set analysis starts from the current data set (if you don't specify set {1} ) and nobody tells set analysis to ignore the Month selection. So that one will still apply...

One word of advice: if you want to create set analysis reductions that operate on a single field in your Master Calendar, and your document has other Master Calendar fields displayed in listboxes as well, take care to reset the others in your set analysis. End-users do unexpected things.