Using IF with set analysis

[#1]

Hello,

I have this expession:

If(year_DI = KPI.Year and month_DI = KPI.Month, Sum({$ <KPI_Reader.Year= ,  KPI_Reader.Month= > } KPI_Work.Sales))

This expression wait for user select and works.

But I need expression (no wait for user select):

Sum (If (year_DI = KPI.Year and month_DI = KPI.Month, {$ <KPI_Reader.Year= ,  KPI_Reader.Month= > } KPI_Work.Sales))

but this not work, the set analyse is problem in this syntax.

Thank you for any help!

Mirek

[sebastiandperei]

why you say that the set part has an error?

Send a reduced copy please..

[#3]

Because first curly bracket is underlined and it's not working.

I cant send it because od sensitive data, sorry. But..

Sum (If (year_DI = KPI.Year and month_DI = KPI.Month, {$ <KPI_Reader.Year= ,  KPI_Reader.Month= > } KPI_Work.Sales))

(If (year_DI = KPI.Year and month_DI = KPI.Month, - This part is about data island and selecting year and month from correct dimension.

{$ <KPI_Reader.Year= ,  KPI_Reader.Month= > } - this part is about set analysis, which make calculation resistant from other selects over other date dimensions.

Mirek

[giakoum]

put the sum in the if statement not outside :

If (year_DI = KPI.Year and month_DI = KPI.Month, sum({$ <KPI_Reader.Year= ,  KPI_Reader.Month= > } KPI_Work.Sales))

[#5]

That's what I have now.. But to start calculate, user must do select.

I need to calculate before select. Here is example:

Sum ( If ( Salesman='Helen', Sales)) - calculating Sales always

but

If (Salesman='Helen', Sum(Sales)) - waiting for user select

[giakoum]

not sure what you mean actually but if you omit the if, user does not need to select :

sum({$<Salesman={"Helen"}>} Sales)

This will always sum sales for Helen even if another salesman is selected, and not Helen.

Not sure if this is what you need.

[jonathandienst]

Hi

Is this what you need (I have just corrected the syntax on the second statement in your original post)?

Sum({<KPI_Reader.Year=, KPI_Reader.Month=>} If (year_DI = KPI.Year and month_DI = KPI.Month,  KPI_Work.Sales))

Hope that helps

Jonathan

PS you could also put what's in the If statement into the set expression, but the syntax depends on whether the items are fields or variables.

[#8]

Hello,

I created the example of my problem. I have 2 different dates in two tables - Reader and Work.

In first table I count fact and to get right number I must select date (Year and Month) from this table (Reader).

In second table I count other two facts and to get correct number I must select date (Year and Month) from this table (Work).

I tried to use Data Island and use set analysis to make expressions resistant for other date dimension select.

Thank you for any help!