topic Re: Doubled value in QlikView

Doubled value

absekrafi — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Doubled value

jobsonkjoseph — Fri, 02 Nov 2018 15:02:49 GMT

Hi Abdallah,

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

Re: Doubled value

achettipalli — Fri, 02 Nov 2018 15:02:59 GMT

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.

Re: Doubled value

absekrafi — Fri, 02 Nov 2018 15:20:18 GMT

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

Re: Doubled value

achettipalli — Fri, 02 Nov 2018 19:24:21 GMT

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

Re: Doubled value

absekrafi — Mon, 05 Nov 2018 10:40:06 GMT

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).

Re: Doubled value

absekrafi — Mon, 05 Nov 2018 10:42:21 GMT

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

Re: Doubled value

raman_rastogi — Mon, 05 Nov 2018 10:56:53 GMT

try this

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

Regards

Raman

Re: Doubled value

absekrafi — Mon, 05 Nov 2018 12:58:37 GMT

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

Re: Doubled value

achettipalli — Mon, 05 Nov 2018 13:21:58 GMT

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

Re: Doubled value

raman_rastogi — Mon, 05 Nov 2018 13:31:27 GMT

Hi Abdallah

May be lke this

Sum(Aggr(sum(if('$(vStartDate)' <= '$(vEndDate)' and TRANSACTION_DATE= '$(vStartDate)' , STOCK, 0)),Dim1,Dim2))

what are you getting in your subtotal? Is it wrong value or Null . Can you share sample data with expected output .

Regards

Raman

Re: Doubled value

absekrafi — Mon, 05 Nov 2018 14:41:36 GMT

Hi All,

I tried this : if (('$(vStartDate)' <= '$(vEndDate)') and {<TRANSACTION_DATE= {'$(vStartDate)'}>}, rangesum(STOCK),0).

The data are correct but the sub-totals aren't when there is data in more than 1 factory (Usine in the image).

Thank you,

Abdallah

Re: Doubled value

absekrafi — Mon, 05 Nov 2018 15:03:27 GMT

When I use rangesum the data is correct but No sub-totals.

When I use Sum, data is wrong, and there are sub-totals.

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