Stock Analysis

[shwetagupta]

Hi All,

I am working on Stock Analysis.

Where a stock is scanned on dealers in a month.
Data looks like below

Account_ID     Date                     Stockqty

1                    23-07-2017               10

2                    24-07-2017               12

2                     25-07-2017               13

2                      25-07-2017               2   

1                    27-07-2017                 9

Expected Result

2                     25-07-2017               15  

1                    27-07-2017                 9

whenever I will check for stock it should provide me the stock scanned on the maximum date of each dealer.

Please suggest the way to get this

For now what  I have done is  aggr(Max(Date),Account_ID) to get the maximum date corresponding to each dealer but it is not giving me the Required Quantity!

[antoniotiman · Accepted solution]

Hi,

Table:
LOAD * Inline [
AccountId,Date,Stockqty
1,23-07-2017,10
2,24-07-2017,12
2,25-07-2017,13
2,25-07-2017,2 
1,27-07-2017,9];
Join LOAD AccountId,Max(Date) as Date,1 as Flag
Resident Table Group By AccountId;
NoConcatenate
LOAD AccountId,Max(Date) as Date,Sum(Stockqty) as Stockqty
Resident Table Where Flag=1
Group By AccountId;
Drop Table Table;

Regards,

Antonio

[pathiqvd]

Hi swetha,

     Try this,

=SUM(IF(Date_1=AGGR(nodistinct MAX(Date_1),Account_ID),Stockqty))

Regards,

[zhadrakas]

Alternatively you can try this:

Dimensions:

Account_ID

aggr(Max(Date), Account_ID)

Expressions:

=Sum({$<Date={"=Max(Date)"}>}Stockqty)

[OmarBenSalem]

Do as follow:

Load  date(max(date)) as date,Account_ID,sum(Stockqty) as Stockqty group by

Account_ID

;

load Account_ID,date(Date#(Date,'DD-MM-YYYY')) as date, Stockqty Inline [

Account_ID,     Date,                     Stockqty

1 ,                   23-07-2017     ,          10

2  ,                  24-07-2017    ,           12

2   ,                  25-07-2017  ,             13

2    ,                  25-07-2017,               2  

1     ,               27-07-2017,                 9

];

result:

[shwetagupta]

Hi Antonio,

Can you please send me the sample qvf of this

[antoniotiman]

See Attachment

[shwetagupta]

Hi Antonio,

Thanks a lot for helping. Solution worked!

[srishsum2017]

Hi Shweta,

Kindly find the qvf file as per your requirement.

Thanks & regards,

Sumit Kumar Srivastava