set expression does not work until selecting particular dimensions

[coloful_architect]

not sure if this is to due with null value or the feature of  different values at a field (opportunity date) I want to build with set expression.

so I have a fact table contains "customer", "contract sales", "Salesdate" 

and then I add a island table by "Customer" to have "opportunity stage", "opportunity date” ; both fields have different stage type or dates for different customers.

I want to build a very simple KPI to aggregate the sales to show

how much sales those customers with the opportunity stage as "A" look like 6 months prior to opportunity date

so I thought this should be a simple set expression like this 

Sum({<
[Opportunity Stage] = {'A'},Saledate = {"<$(=AddMonths(opportunity date, -6))"}
>} [Contract Sales])

but the KPI wont return anything unless I hit specific customer and select A at the field of Opportunity Stage.

and idea what I am missing?

I tried to left join the table to stack opportunity date and type on the FACT table, still does not work.

please advise.

[Chanty4u]

Try this 

Sum({<

[Opportunity Stage] = {'A'},

Salesdate = {"<$(=Max({<[Opportunity Stage]={'A'}>} [Opportunity Date]) - 6)"}  

>} [Contract Sale

s])

[coloful_architect]

but if using MAX(), that turns to find the single latest time among all the opportunity date and from there to calculate the sales. I am looking for the aggregation for sales from those customers who they have different opportunity date.  one could be Feb, one could be Mar, I want to have the sum to include all of them who they have different opportunity dates. 

[Oleg_Troyansky]

Hi,

It appears to me that you misunderstand how Set Analysis works (no offence!). Your condition could work within an IF() function, which would be performed for each detailed row of the data, hence comparing the two dates would be valid.

Set Analysis conditions, however, are validated globally, outside of the scope of your chart. So, when you are comparing Sales Date to the Opportunity Date - 6 months, this comparison is not done at the individual data row level, but rather globally, and that is why the expression doesn't produce any data until you make selections to render a unique value of the opportunity date.

So, if I were to offer the solution, I'd suggest one of the two - if the data is relatively small (less than a few millions of rows), then use the IF() function, even though it's slower than using Set Analysis.

If your data can be bigger than that, then try to calculate this condition as a conditional flag in the script and use the flag in Set Analysis. Also, with larger data sets avoid data islands at all costs - they will eventually bring your server down. Definitely link your tables by Customer, to improve performance.

Cheers,

[coloful_architect]

none take. come here to learn. so Oleg, can you give me a rough example of set expression if the data set is small? how do I do the If () ...just a broad way if you dont mind

and regarding the script load version, what kind of conditional flag you suggest, mind elaborating a bit more? 

[Oleg_Troyansky · Accepted solution]

Hi,

For a small dataset, I'd use the following IF condition:

SUM( IF(Date1<Date2, Sales))

To calculate this as a conditional flag in the script, I'd use something like this (assuming that both dates are stored in the same table):

LOAD

...,

IF(Date1 < Date2, 1, 0)    as _Date_Flag,
...

Then, your set analysis expression would look like this:

Sum( {<_Date_Flag={1}>} Sales)

These are very basic developer techniques that all new developers learn either by attending a training class, or by reading one of the tutorial books for beginners (like mine).

Cheers,