Sum leads to false values.

[psk180590]

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

[tresesco]

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;

[psk180590]

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%

[tresesco]

Could you post a sample qvw?

[psk180590]

Attached Data and App.

Thanks!

[PrashantSangle]

Try,

Sum(Total<Market> Sales)

Regards,

[psk180590]

Hi Prashant,

You mean in the Join?

If so then i should also do a GroupBy Market?

Thanks!!

[PrashantSangle]

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

[tresesco]

Try using second expression with DISTINCT keyword like :

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

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

[kaushiknsolanki · Accepted solution]

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