topic Re: Set Analysis in QlikView

Set Analysis

Zaga_69 — Sat, 16 Nov 2024 05:05:50 GMT

Hi all,

I have a Filter pane in my dashboard called "YearMonth". I created this field in the Master calendar as follow:

Date(monthstart(TempDate), 'YYYYMM') As YearMonth

I use the first formula and it works, however the second formula does not work.

1. sum({$<YearMonth={"<201405"}>}YCOUNT)

2. sum({$<YearMonth={"<(=date(YearMonth))"}>}YCOUNT)

in the second one I wanted to sum the field YCOUNT for all periods lower than the selected one in the Filter pane.

Any idea what is wrong with formula structure?

Best regards,

Edi

Re: Set Analysis

agigliotti — Fri, 30 Nov 2018 09:44:48 GMT

maybe this:
sum( {< YearMonth = {"<$(=Max(YearMonth))"} >} YCOUNT )
I hope it helps.

Re: Set Analysis

sunny_talwar — Fri, 30 Nov 2018 12:40:38 GMT

Or this

 Sum({$<YearMonth = {"<$(=Date(Max(YearMonth), 'YYYYMM'))"}>} YCOUNT)

Re: Set Analysis

Zaga_69 — Fri, 30 Nov 2018 12:44:34 GMT

Hi Agigliotti,

It works, however I need to include more conditions and I am struggling with it. The image below shows you what I want to calculate. I need to calculate the tree components of the measure called "Total not on Time".

So far I was able to calculate the first component using the following formula:

+Sum({<DOT_STATUS={'Too Late'},YearMonth={$(=YearMonth)},NotDeliveredYet={0}>}YCOUNT)

However when I tried to calculate the second and third component, the following formula does not work:

sum({<DOT_STATUS={'Too Late'},YearMonth={"<$(=Max(YearMonth))"}, NotDeliveredYet={1}>}YCOUNT ) // this part works

+Sum({<DOT_STATUS={'Too Late'},YearMonth={"<$(=date(PlannedGoodsMov))"},NotDeliveredYet={0},YearMonth={">$(=date(ActualGoodsMov))"}>}YCOUNT)

Any idea?

Thanks in advance,

Best regards,

Edi

Re: Set Analysis

agigliotti — Fri, 30 Nov 2018 13:52:44 GMT

taking a look at your expression below, maybe something is wrong in it.
Sum({<DOT_STATUS={'Too Late'},YearMonth={"<$(=date(PlannedGoodsMov))"},NotDeliveredYet={0},YearMonth={">$(=date(ActualGoodsMov))"}>}YCOUNT)

Why do you have field name "YearMonth" twice ?

Re: Set Analysis

Zaga_69 — Fri, 30 Nov 2018 14:07:15 GMT

I do not see the mistake 🙂

YearMonth correspond to the Current Selected Period by the user in the Dashboard.

Perhaps to clarify it is important to mention that the YearMonth filed was built based on PlannedGoodsMov column.

What I am trying to calculate in this formula is all delivery items which has the flag "Too Late" and the Current selected period (i.e. the YearMonth variable) is lower than the PlannedGoodsMov and higher than the ActualGoodsMov.

The image below shows you a clear example: The period selected is 201606 and then the items was supposed to be delivered on 4/28/2016 (which is lower that the current selected period) but was delivered on 7/1/2016 (which is higher than the current selected period).