Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

suhasinigm
New Contributor III

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

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.

14 Replies
itec_pao
Contributor

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

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
New Contributor III

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

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
Esteemed Contributor

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

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
New Contributor III

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

Hi Sir,

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

YoussefBelloum
Esteemed Contributor

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

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
Esteemed Contributor

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

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
New Contributor III

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

Okay,I have created the Calendar.

suhasinigm
New Contributor III

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

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
Contributor

Re: Prod_Id wise max date from the selected date range and 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

Community Browser