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.

[itec_pao]

Hi Suhasini,

One question, why do you have to date fields? One would be enough. Then based on the selection you could better provide the data. Because in this case, you only have to consider the To_Date, ignore the From_Date within the set analysis.

Regards,

Oktay

[suhasinigm]

Hi Sir,

I tried with To_Date in the Sales Column ,but i am not able to get,

can you please help me in this.

[YoussefBelloum]

Hi,

you have a modelization problem here.

your tables are disconnected.

Also, here you simply need a Calendar table to be able to exploit your measures using time axis.

Add this script instead of the "From &To" script and REMOVE the month field from your Data script:

temp:

load max(date) as max_data,

min(date) as min_data

resident Test;

LET varMinDate = Num(Peek('min_data', 0, 'temp'));

LET varMaxDate = Num(Peek('max_data', 0, 'temp'));

TMP_DATE:

LOAD date(date#('$(min_data)','DD/MM/YYYY')-1 + recno() ,'DD/MM/YYYY') as Date

AUTOGENERATE (date#('$(max_data)','DD/MM/YYYY') - date#('$(max_data)','DD/MM/YYYY')) + 1 ;

DATE_TABLE:

LOAD

date(Date) as Date,

Year(Date) as Year,

Month(Date) as Month,

num(Month(Date)) as Monthnumber

RESIDENT TMP_DATE;

[suhasinigm]

Hi Sir,

In This case how i will give the option to select the date range.

[YoussefBelloum]

Yes, Calendar table is the perfect way to play with time axis

start by adding this table and i'll show examples of expressions you can use related to range etc

[YoussefBelloum]

My bad, I thought your question was: Will I be able to...

yo will be able to select a date range using variables and triggers (one way to do it)

[suhasinigm]

Okay,I have created the Calendar.

[suhasinigm]

Hi Sir,

By using this expression i am getting max Date:aggr(max( {$<Date = P({1<Date={"<=$(=max(ToDate))"}>} Date)>} Date ),Prod_Id),

How would i get the Sales for that Date.

[itec_pao]

The date range you can also have if you have just one date field. then you click on the filter and write e.g. >=01.01.2017 <=01.02.2017

This makes it simpler for selecting over just one field, and for the implementation of the charts also.

Regards,

Oktay