Error When Adding Time Dimension

[#1]

Hello

I'm traying to bulid a pivot table with the sales amount by store, but only for the store that last week the amount of sales is grater than $ 500, for this porpuse I créate de following formula:

If(Sum( {1<FECHA = {">=$(=WeekStart(Date(Today()-7))) <=$(=WeekEnd(Date(Today()-7)))"}>} TOT_COMISION_COMPARTIDA) > 500,sum(TOT_COMISION_COMPARTIDA), 0)

Every thing goes fine when I use the Pivot Table with only one dimensión TIENDA:

The problems begin in I try to add a time dimensión to the table (Year, Month, Week, etc.) because the Table restrict me the values only to the dates defined in the formula, but I want the formula only for the condition, not for the definition of my data universe

Somebody help me please

Best Regards

[sunny_talwar]

What results are you expecting to see for the other dates?? Your expression is saying that when Sum( {1<FECHA = {">=$(=WeekStart(Date(Today()-7))) <=$(=WeekEnd(Date(Today()-7)))"}>} TOT_COMISION_COMPARTIDA) is greater than 500, show sum(TOT_COMISION_COMPARTIDA) otherwise show 0.

What do you intend to see???

[ramoncova06]

in the presentation tab remove the suppress zero

though Sunny is right, everything outside of that expression is going to be zero

[#4]

I want to see all the sells data for the stores (no date restricctions), all history, but only for those stores that the sells for the last week are grather than $ 500

[#5]

I want to see all the sells data for the stores (no date restricctions), all history, but only for those stores that the sells for the last week are grather than $ 500

[ramoncova06]

ok, in that case you need to use a search in your expression

sum({<Tienda =p(Tienda = {"Sum( {< FECHA = {">=$(=WeekStart(Date(Today()-7))) <=$(=WeekEnd(Date(Today()-7)))"}>}

TOT_COMISION_COMPARTIDA) > 500)"}>}TOT_COMISION_COMPARTIDA)

[#7]

Thanks Ramón

Are you sure the expression is OK, send me an error.....

Is Tienda =p OK???

sorry but I'm new in this

Thanks in advance for your help

[#8]

sum({<Tienda =p(Tienda = {"Sum( {< FECHA = {">=$(=WeekStart(Date(Today()-7))) <=$(=WeekEnd(Date(Today()-7)))"}> TOT_COMISION_COMPARTIDA) > 500)"}>}TOT_COMISION_COMPARTIDA)

Somethig is wrong can you help me with the sctructure

Thanks

[ramoncova06 · Accepted solution]

the p is ok, I guess I missed with a bracket since it has a lot of them

this is working fine for me

sum({<Tienda =p({<Tienda = {"=sum({<FECHA = {'>=$(=weekstart(today(),-1))<=$(=weekend(today(),-1))'}>}TOT_COMISION_COMPARTIDA) > 500"}>})>} TOT_COMISION_COMPARTIDA)

[#10]

It Works

Geat thanks for all