topic Re: Calculation in QlikView

Calculation

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

Hi All,

When I select fiscal year and fiscal period 1-9 as a group I get a total of 3,596,680. However, when I select fiscal year and fiscal period 1-9 are selected individually, they total 4,157,575. Please let me know how I can fix this issue

My expression is:

=sum(cash_price)

Re: Calculation

sunny_talwar — Thu, 15 Nov 2018 14:50:12 GMT

Not sure I follow the difference in selection... what do you mean when you select fiscal year and fiscal period as a group?

Re: Calculation

chrismarlow — Thu, 15 Nov 2018 14:50:57 GMT

Hi,

Maybe a duplicate somehow between your dates and your facts (assuming you have a date table separate).

Can you post an image/values for a straight table with fiscal_period/year as dimensions and sum(cash_price) as expression?

Cheers,

Chris.

Re: Calculation

has75042 — Thu, 15 Nov 2018 14:51:28 GMT

for example

fiscal year 2019

fiscal year 1-9 all at once

Re: Calculation

has75042 — Thu, 15 Nov 2018 15:01:09 GMT

when you add all them in excel total comes upto be 4,157,575.00

Re: Calculation

chrismarlow — Thu, 15 Nov 2018 16:10:46 GMT

Please can put a picture of your data model (control-t) should do it.

Cheers,

Chris.

Re: Calculation

has75042 — Thu, 15 Nov 2018 16:13:43 GMT

Re: Calculation

chrismarlow — Thu, 15 Nov 2018 17:00:52 GMT

Hi,

I think you have a buyback_id that appears with more than one Dateonly - where the date then sits in a different fiscal period/year.

So I can create a toy model with similar structure & make the data do that (attached, but I am on personal edition) and so come up with a non summing straight table (left);

Possibly you could add something like the grid on the right & see if you get count distinct>1?

Cheers,

Chris.

Re: Calculation

has75042 — Thu, 15 Nov 2018 19:11:13 GMT

Chris,

Somehow I can't open this QW

Re: Calculation

has75042 — Thu, 15 Nov 2018 19:20:48 GMT

You are right it does show in two fiscal period. Is there a way to fix this issue

Re: Calculation

chrismarlow — Thu, 15 Nov 2018 19:54:18 GMT

Hi,

It depends what the cause of this is & how the rest of the reporting on this model is supposed to behave.

First thing to check is it valid for buyback_id to have 2 dates, or has something gone wrong in the production of the table in the model to introduce incorrect additional rows.

If it is valid then you need a business rule for what to do in these circumstances for this aggregation, for example;

1) Use the last FP, or,

2) Use the first FP, or,

3) Apportion across the different FP

When you know that you would have to think is (a) you want to fix in script (say by tagging items in Ledger table with a flag & including that flag in the formula), or (b) if a more complex formula would do it - say it is only here where the issue needs fixed.

Cheers,

Chris.