Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
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;
Hi Sir,
In This case how i will give the option to select the date range.
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
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)
Okay,I have created the Calendar.
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.
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