Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

ak00428233
New Contributor III

To find the units of stock when we know the quantity and units sold

Hi ,

In my scenario, explaining briefly a car owner have a particular model 140 cars and when a customer purchased the no.of cars remaining will be 139.So for the next customer i have entered quantity as 139 in fact table .Like wise i have entered reducing the quantity for 1300 customers .So if i want to find the total quantity it was adding total the 140+139+138 of the same car which was giving wrong result.So its difficult to find the units in stock.Could anyone suggest an expression which gives only the remaining quantity  of that particular car.

Pls help me if you know this

regards

Anuraj

1 Solution

Accepted Solutions
balrajahlawat
Esteemed Contributor

Re: To find the units of stock when we know the quantity and units sold

You can do like this as well?

Test:

load * Inline

[ModelID, Quantity, Sold

1001, 140,1

1001,139,1

1001,138,1

];

Test1:

load

ModelID,

max(Quantity)-sum(Sold) as StockValue

Resident Test

Group by ModelID;

Expression in Text Box:  sum(StockValue)

10 Replies

Re: To find the units of stock when we know the quantity and units sold

Try adding a field with the value 1 for each record: 1 As Sold. When you sum that field you'll have the number of sold cars.


talk is cheap, supply exceeds demand
ak00428233
New Contributor III

Re: To find the units of stock when we know the quantity and units sold

Yes Gysbert I am getting the number of Sold Cars but how can i get the number of cars which were in stock.If i Write the expression Sum(Quantity) which is adding 140+139+138 of that same car.But i need to show it as if i have 140 as my intitial quantity and 3 cars are sold .To have the units in stock it should do as 140-3 which will be the units of stock.

Actually in the fact table it is like

Model_ID       Customer_ID        Quantity    Units Sold

M001                    2001                      140           1

M001                    2002                       139           1

M001                    2003                        138           1.

Can you suggest an expression through which i can get the initial quantity-Sum(Units Sold)

balrajahlawat
Esteemed Contributor

Re: To find the units of stock when we know the quantity and units sold

try like this?

In Stock Expression= aggr(max(Quantity),  Model_ID) - aggr(sum(UnitSold), Model_ID)

ak00428233
New Contributor III

Re: To find the units of stock when we know the quantity and units sold

Thank you Balraj It was giving the units in stock result.But it was giving when i selected an Model_ID.Can you tell the expression to give the overall stock in the company which includes all model_ID'S

balrajahlawat
Esteemed Contributor

Re: To find the units of stock when we know the quantity and units sold

Take Model_ID as Dim in Pivot/Straight or Chart, and above mentioned expression as Stock expression

ak00428233
New Contributor III

Re: To find the units of stock when we know the quantity and units sold

Hi Balraj,

But i want the value of units of stock to be displayed in a text object in this sheet to represent the whole information of the companycust.PNG

balrajahlawat
Esteemed Contributor

Re: To find the units of stock when we know the quantity and units sold

see the attachment, its working for me without any selection....

balrajahlawat
Esteemed Contributor

Re: To find the units of stock when we know the quantity and units sold

You can do like this as well?

Test:

load * Inline

[ModelID, Quantity, Sold

1001, 140,1

1001,139,1

1001,138,1

];

Test1:

load

ModelID,

max(Quantity)-sum(Sold) as StockValue

Resident Test

Group by ModelID;

Expression in Text Box:  sum(StockValue)

ak00428233
New Contributor III

Re: To find the units of stock when we know the quantity and units sold

Thank you so much Balraj now i am getting the stock value by including Test1.specify.PNG

Community Browser