topic Re: Set analysis based on date selection in QlikView

Set analysis based on date selection

Anonymous — Mon, 22 May 2017 16:09:09 GMT

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

Re: Set analysis based on date selection

Anonymous — Mon, 22 May 2017 17:47:44 GMT

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.

Re: Set analysis based on date selection

Anonymous — Mon, 22 May 2017 20:43:00 GMT

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

Re: Set analysis based on date selection

Anonymous — Tue, 23 May 2017 11:49:26 GMT

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.

Re: Set analysis based on date selection

Anonymous — Tue, 23 May 2017 11:50:22 GMT

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.

Re: Set analysis based on date selection

Anonymous — Tue, 23 May 2017 14:39:31 GMT

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

Re: Set analysis based on date selection

Anonymous — Wed, 24 May 2017 07:49:17 GMT

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.

Re: Set analysis based on date selection

Peter_Cammaert — Wed, 24 May 2017 09:14:24 GMT

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

Re: Set analysis based on date selection

Anonymous — Wed, 24 May 2017 09:44:26 GMT

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!

Re: Set analysis based on date selection

Peter_Cammaert — Wed, 24 May 2017 10:03:27 GMT

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.

Re: Set analysis based on date selection

Anonymous — Wed, 24 May 2017 10:59:54 GMT

I'll play a little with the MasterCalendar to find out what you mean.

Thanks for your effort!

Re: Set analysis based on date selection

maryshea — Mon, 03 Feb 2020 18:16:22 GMT

I am simply trying to pull the quanity oh hand value on a past date for an item.

I am trying sum({$<[ItemHistory.Txn Date]={"<=2020-01-31"}>}[ItemHistory.Quantity On Hand]) but it is not returning the correct quantity and does not change values with a change of date.

Do you have thoughts on a solution?