Doubled value

[absekrafi]

Dear Community members,

I have the exression bellow for "Quantity in Stock" in a pivot table :

if (('$(vStartDate)' <= '$(vEndDate)'),sum({<TRANSACTION_DATE= {'$(vStartDate)'}>}STOCK),0).

I am getting a value doubled for 1 product.

When I added a "table zone" to the sheet project, I got the correct value.

I am not understanding where is the mistake.

Do you have any idea where the problem is?

Thank you in advance,

Abdallah

[jobsonkjoseph]

Hi Abdallah,

Just check if the variables are set properly, it might be the issue.

[achettipalli]

Hi Abdallah,

That expression seems legit.

May I know what is "Table zone" here?

and it'd be great if you post with a sample file.

[absekrafi]

Hi Avinash, Hi Jobson,

"Table zone" means "table area" when you right ckick to add a new object to the current sheet.

For the sample file, I have too many QVD files. So I think it will not be easy to enclose them.

I will check the the variables : vStartDate and vEndDate as highlighted by Jobson.

I will let you know,

Many thanks,

Abdallah

[achettipalli]

Alright!. I'm not sure if you're comparing with straight table vs pivot table results and how many dimensions you're dealing with. And sometimes our expected results would contradict with table row properties itself.

Good luck

Avinash

[absekrafi]

It's true, the staright table doesn't contain expressions. I am displaying the same dimensions in both tables.

When I change the expression, in the pivot table to the dimension STOCK, I get good results.

Just to mention the error isn't with one expression only (too many expressions show the same error).

[absekrafi]

The variables are OK. I have 2 variables only, vStartDate and vEndDate only. I am still in the case where, vStartDate = vEndDate.

[raman_rastogi]

try this

Sum(If($(vStartDate) <= $(vEndDate) and  TRANSACTION_DATE=  $(vStartDate)  ,STOCK))

Regards

Raman

[absekrafi]

Hi Raman,

The expression bellow is working but the sub-totals aren't.

if('$(vStartDate)' <= '$(vEndDate)'  and TRANSACTION_DATE=  '$(vStartDate)'  , STOCK, 0)

Just to let you know that the cause of the error is the Sum(). but I don't know why.

Thank you,

Abdallah

[achettipalli]

As I mentioned, with a sample Dataset/Qvw file would help us to understand the problem better.

However, different workarounds,

1. Could be with check/uncheck options from Presentation tab like suppress null values etc..

2. can try AGGR(sum(), dimesion)

3. Unit testing with a table row results while having date and other dimensions as filters.

Good luck

Avinash