Prod_Id wise max date from the selected date range and the sales for that date.

[suhasinigm]

Hi All,

I want Prod_id wise max Date from the selected Range(From _Date and To_Date),and I want the Sales for that Prod_id  for the selected range.

Can any one help me to resolve that issue.I have attached the sample QVF.

example:I have selected From_Date:01/01/2018 and To_Date:01/02/2018

I want Output Should be  as below:

Prod_id , Date,   Sales

101,  01/02/2018,   20

102,  01/01/2018,  30

103, 01/02/2018,   30.

[suhasinigm]

Hi Sir,

I Got Max Date,But I am not getting Sales value for that,

I wrote the this expression:=aggr(max( {$<Date = P({1<Date={"<=$(=max(ToDate))"}>} Date)>} Sales ),Prod_Id).

I am not getting how to display the sales value for ,same qvf i have attached.

[itec_pao]

HI suhasini,

For a given Prod_ID you want to get following expressions:

Max_Date:     max(Date)

Sales:             Sum(If(Aggr(NODISTINCT Max(Date), Prod_Id) = Date, Sales))  

[suhasinigm]

Hi Sir,

I tried with this expression its not working,its giving me the Min Sales by Prod_Id irrespective of Date selection.

Example If I select From_Date:01/01/2018 and To_Date : 01/02/2018

the output should be

Prod_Id,  Date,      Sales

101,     01/01/2018,  20

  102,    01/02/2018,30

103,      01/02/2018, 30

Thank You.

[itec_pao]

In your chart, I put max(Date) for Date and Sum(If(Aggr(NODISTINCT Max(Date), Prod_Id) = Date, Sales))  for the Salss. You have to delete the existing Sales field, because you have created a dimension instead of an expression. Therefore, delete Sales field, create a new expression with Sum(If(Aggr(NODISTINCT Max(Date), Prod_Id) = Date, Sales))  .

You can ignore the From_Date and To_Date and just use the Date of the Test table and selecting by typing >=01/01/2018 <01/03/2018

[suhasinigm]

Hi Sir,

Thank You  so much for your assist,I got the solution.

The expression which I used is as below.

Sum(If(Aggr(NODISTINCT Max( {$<Date = P({1<Date={">=$(=max(FromDate))<=$(=max(ToDate))"}>} Date)>} Date ), Prod_Id) = Date, Sales))