topic Re: Error When Adding Time Dimension in QlikView

Error When Adding Time Dimension

Wed, 17 Jun 2015 19:42:30 GMT

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

Re: Error When Adding Time Dimension

sunny_talwar — Wed, 17 Jun 2015 20:03:38 GMT

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???

Re: Error When Adding Time Dimension

ramoncova06 — Wed, 17 Jun 2015 20:28:34 GMT

in the presentation tab remove the suppress zero

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

Re: Error When Adding Time Dimension

Wed, 17 Jun 2015 20:57:55 GMT

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

Re: Error When Adding Time Dimension

Wed, 17 Jun 2015 20:58:55 GMT

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

Re: Error When Adding Time Dimension

ramoncova06 — Wed, 17 Jun 2015 21:10:55 GMT

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)

Re: Error When Adding Time Dimension

Wed, 17 Jun 2015 21:18:03 GMT

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

Re: Error When Adding Time Dimension

Wed, 17 Jun 2015 22:16:18 GMT

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

Re: Error When Adding Time Dimension

ramoncova06 — Wed, 17 Jun 2015 22:19:40 GMT

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)

Re: Error When Adding Time Dimension

Thu, 18 Jun 2015 15:19:04 GMT

It Works

Geat thanks for all