topic Product wise Total in QlikView

Product wise Total

Wed, 25 Nov 2020 16:16:04 GMT

Dear All

I need help on below Requirement:

Any help would be appreciated.

Regards

ERic

Re: Product wise Total

PrashantSangle — Mon, 25 Jul 2016 10:04:19 GMT

Hi,

sum(Value)/sum(Total<Product> Value)

Regards

Re: Product wise Total

Anonymous — Mon, 25 Jul 2016 10:17:04 GMT

Hi Eric,

Create Straight Table

Dimension : Product

Expression : Sum(Value)/Sum(TOTAL Value)

Hope it helps!!

Re: Product wise Total

susovan — Mon, 25 Jul 2016 10:34:19 GMT

Hi Eric,

Try this attachment,

Re: Product wise Total

susovan — Mon, 25 Jul 2016 10:47:49 GMT

I have used this below expression for Logic:

if(Product='CAR',Sum({<Product={'CAR'}>}Value)&'/'&Sum(TOTAL{<Product={'CAR'}>}Value),

if(Product='TEMPO',Sum({<Product={'TEMPO'}>}Value)&'/'&Sum(TOTAL{<Product={'TEMPO'}>}Value),

if(Product='TRUCK',Sum({<Product={'TRUCK'}>}Value)&'/'&Sum(TOTAL{<Product={'TRUCK'}>}Value),

if(Product='BIKE',Sum({<Product={'BIKE'}>}Value)&'/'&Sum(TOTAL{<Product={'BIKE'}>}Value)))))

The expression of Desired Result:

if(Product='CAR',Sum({<Product={'CAR'}>}Value)/Sum(TOTAL{<Product={'CAR'}>}Value),

if(Product='TEMPO',Sum({<Product={'TEMPO'}>}Value)/Sum(TOTAL{<Product={'TEMPO'}>}Value),

if(Product='TRUCK',Sum({<Product={'TRUCK'}>}Value)/Sum(TOTAL{<Product={'TRUCK'}>}Value),

if(Product='BIKE',Sum({<Product={'BIKE'}>}Value)/Sum(TOTAL{<Product={'BIKE'}>}Value)))))

Re: Product wise Total

PrashantSangle — Mon, 25 Jul 2016 10:51:36 GMT

Hi,

what is your expression if you have 100 different of products???

Regards

Re: Product wise Total

susovan — Mon, 25 Jul 2016 11:03:48 GMT

Hi Max,

Thanks for your reply.

According to my expression it will be huge.

Re: Product wise Total

Mon, 25 Jul 2016 11:14:14 GMT

Thank You Sir.

Can we do it from Backend Script using Rangesum or any other function.

Re: Product wise Total

tyagishaila — Mon, 25 Jul 2016 11:16:34 GMT

Sum(Value)/ Sum(Total <Product> Value)

Re: Product wise Total

PrashantSangle — Mon, 25 Jul 2016 11:19:20 GMT

Hi,

I just want to point out there is limitation with if - else so avoid it if you can.

Regards

Re: Product wise Total

PrashantSangle — Mon, 25 Jul 2016 11:21:06 GMT

Hi,

In script You need to use simple sum() with group by product.

Regards,

Re: Product wise Total

susovan — Mon, 25 Jul 2016 11:56:21 GMT

Thank you sir for your valuable suggestion ,

Re: Product wise Total

Mon, 25 Jul 2016 12:32:19 GMT

Hi Eric,

Try this in script..

testtttt:

LOAD Proposal,

Value,

Product

FROM

Sample_Test.xlsx

(ooxml, embedded labels, table is Sheet1);

Left Join(testtttt)

LOAD Product,sum(Value) as total Resident testtttt group by Product;

final:

LOAD *,Value/total as [Desired Result] Resident testtttt;

drop table testtttt;

Thanks,

Re: Product wise Total

sunny_talwar — Mon, 25 Jul 2016 13:00:42 GMT

For backend you can try this:

Table:

LOAD * INLINE [

Proposal, Value, Product

1111, 1000, CAR

1112, 2000, CAR

1113, 3000, TRUCK

1114, 4000, TRUCK

1115, 5000, TRUCK

1116, 6000, TEMPO

1117, 7000, TEMPO

1118, 8000, BIKE

];

MappingTable:

Mapping

LOAD Product,

Sum(Value)

Resident Table

Group By Product;

FinalTable:

LOAD *,

Value/ApplyMap('MappingTable', Product) as [Desired Result]

Resident Table;

DROP Table Table;