Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Set analysis based on date selection

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


1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

View solution in original post

11 Replies
Anonymous
Not applicable
Author

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
Not applicable
Author

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

Anonymous
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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

Anonymous
Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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
Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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.