topic Re: Sum leads to false values. in QlikView

Sum leads to false values.

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

Hello All,

I have a straight excel table with some Market, Product, Year, Month and Sales values.

Market	Product	Year	Month	Sales
1	AA	2017	Jan	100
1	BB	2017	Feb	200
2	FR	2017	Jan	300

Script:

Temp:

Load

Market,

Product,

Year,

Month,

Sales

From Sample.xls;

Join(Temp)

Load

Market,

Year,

Month,

Sum(Sales) as MarketSales

Resident Temp

Group by Market, Year, Month;

After the load my final table which i'm saving in an qvd looks this.

Market	Product	Year	Month	Sales	TotalMarket
1	AA	2017	Jan	100	300
1	BB	2017	Feb	200	300
2	FR	2017	Jan	300	300

Up to this everything looks fine.

In the next step i'm looking to display this results in my application via an Table(All the results) and also a graph(Only Market Share of each Product).

And here i have couple of Expressions - Sum(Sales) as ProductSales and Sum(TotalMarket) as MarketSales.

ProductSales works fine but the MarketSales gives me wrong values(Ex: Here for Market 1 it is giving me 600).

Can you please help me with writing a formula where the original values of TotalMarket(300 for Market 1) will be displayed instead of 600.

TIA!!

Re: Sum leads to false values.

tresB — Fri, 30 Jun 2017 07:17:31 GMT

Try grouping the sales only on Market like:

Temp:

Load

Market,

Product,

Year,

Month,

Sales

From Sample.xls;

Join(Temp)

Load

Market,

Sum(Sales) as MarketSales

Resident Temp

Group by Market;

Re: Sum leads to false values.

psk180590 — Fri, 30 Jun 2017 07:24:46 GMT

Hello Tresesco,

Thanks for the quick suggestion. Even this didn't help me.

Here the first and second columns should be same and in the third column first Row it should display 100%

Re: Sum leads to false values.

tresB — Fri, 30 Jun 2017 07:26:33 GMT

Could you post a sample qvw?

Re: Sum leads to false values.

psk180590 — Fri, 30 Jun 2017 07:40:12 GMT

Attached Data and App.

Thanks!

Re: Sum leads to false values.

PrashantSangle — Fri, 30 Jun 2017 07:47:35 GMT

Try,

Sum(Total<Market> Sales)

Regards,

Re: Sum leads to false values.

psk180590 — Fri, 30 Jun 2017 07:52:16 GMT

Hi Prashant,

You mean in the Join?

If so then i should also do a GroupBy Market?

Thanks!!

Re: Sum leads to false values.

PrashantSangle — Fri, 30 Jun 2017 07:58:00 GMT

Where do you want to achieve it in Front end or in back end??

Solution which i have provided work in front end

in back end Tresesco solution will work for you in his solution add group by field on which level you want to group it.

Regards,

Prashant

Re: Sum leads to false values.

tresB — Fri, 30 Jun 2017 07:59:08 GMT

Try using second expression with DISTINCT keyword like :

sum(DISTINCT {<Year={$(vYear)}>} SalesMarket)

However, this could have been done easily without this complexity.

Re: Sum leads to false values.

kaushiknsolanki — Fri, 30 Jun 2017 08:04:57 GMT

Hi,

You dont need to create the total in script.

You can do that in chart only.

Have a look at the app attached.

Regards,

Kaushik Solanki