Set Analysis

[Zaga_69]

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

[agigliotti]

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

[sunny_talwar]

Or this

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

[Zaga_69]

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

[agigliotti]

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 ?

[Zaga_69]

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).