topic Re: Set Analysis ( Date ) in QlikView

Set Analysis ( Date )

ch_riadh — Sun, 19 Oct 2014 11:48:58 GMT

i want to create this expression:

If Status = {'A'} and Received_date_field > vSelectDate and Invoiced_date_field < vSelectDate , Count (StockID)

vSelectDate is a variable : Max(Received_date_field)

Please need help emergency

Thanks

Re: Set Analysis ( Date )

Sun, 19 Oct 2014 11:59:47 GMT

Hi,

Try this expression

=count({<Status = {'A'}, Received_date_field={'>$(=Max(Received_date_field))'}, Invoiced_date_field={'<$(=Max(Received_date_field))'}>}StockID)

Re: Set Analysis ( Date )

ashfaq_haseeb — Sun, 19 Oct 2014 12:02:24 GMT

Hi,

Try like this

Count({$<Status = {'A'},[Received_date_field]={">=$(vSelectDate)"},[Invoiced_date_field]={"<=$(vSelectDate)"}>} [StockID])

Make sure you have proper date format.

Regards

ASHFAQ

Re: Set Analysis ( Date )

ch_riadh — Sun, 19 Oct 2014 12:26:17 GMT

Thanks but, i want , if i select the month exp: March 2013 without selecting the date it give me 31/03/2014.and i need all data from 2008 till the date who i selected.

Re: Set Analysis ( Date )

ashfaq_haseeb — Sun, 19 Oct 2014 12:30:33 GMT

Hi,

Option 1: = monthend(max(Date) )

Option 2: = monthend(makedate(Year, Month) )

Regards

ASHFAQ

Re: Set Analysis ( Date )

ch_riadh — Sun, 19 Oct 2014 12:45:15 GMT

This is my expression: i need all data from 2008 till the date who i selected.Can you give me the full expression please.thanks

Count({$<Status = {'A'},[Received_date_field]={">=$(vSelectDate)"},[Invoiced_date_field]={"<=$(vSelectDate)"}>} [StockID])

Variables: vStartDate : 31/12/1988

vSelectDate: Max(Received_date_field)

vMonthEnd: MonthEnd(Received_date_field)

Re: Set Analysis ( Date )

ashfaq_haseeb — Sun, 19 Oct 2014 12:47:32 GMT

Hi,

Can you post sample app with expected o/p

Regards

ASHFAQ

Re: Set Analysis ( Date )

ch_riadh — Sun, 19 Oct 2014 12:56:37 GMT

Variables: vStartDate : 31/12/1988

vSelectDate: Max(Received_date_field)

vMonthEnd: MonthEnd(Received_date_field)

Re: Set Analysis ( Date )

ashfaq_haseeb — Sun, 19 Oct 2014 13:02:43 GMT

Hi,

Why 2008 is your data start from 2008 or what?

Regards

ASHFAQ

Re: Set Analysis ( Date )

ch_riadh — Sun, 19 Oct 2014 13:10:35 GMT

yes
i wanna get the all data till selected date

Re: Set Analysis ( Date )

ch_riadh — Sun, 19 Oct 2014 13:12:09 GMT

sorry From 1988

Re: Set Analysis ( Date )

ashfaq_haseeb — Sun, 19 Oct 2014 13:16:17 GMT

Hi,

Basically what I understand is you need data from starting till month end of selected year and month.

Correct me if I misunderstood something

Regards

ASHFAQ

Re: Set Analysis ( Date )

ch_riadh — Sun, 19 Oct 2014 13:16:49 GMT

yes

Re: Set Analysis ( Date )

ch_riadh — Sun, 19 Oct 2014 13:19:40 GMT

So give me the available stock.
if i select for example March 2012.
you have to give me all data till 31/03/2012
with this condition : Invoice_Date > Selected date.

Re: Set Analysis ( Date )

ashfaq_haseeb — Sun, 19 Oct 2014 13:24:53 GMT

Hi,

You need to create one common date for that have a look at below post

http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

Then try below

vSelectDate= monthend(Max(Received_date_field))

Count({$<Status = {'A'},[CommonDate]={"<=$(vSelectDate)"}>} [StockID])

Regards

ASHFAQ

Re: Set Analysis ( Date )

ch_riadh — Mon, 20 Oct 2014 10:14:06 GMT

LOAD

Status,

Stockid,

Model,

RECDATE,

INVDATE

FROM

(qvd) ;

My Customer want to show the Available Stock till the date selected.
with this Condition :

STATUS = "I" and RECDATE <= Selected_Date and INVDATE > Selected_Date

For example :
if i select ( march/2014),

i need to see : the stock available stock from 1900 til 31/03/2014.

and if a car received on 20/03/2014 and invoiced on 02/04/2014 -----> i have to show it.

but if a car received on 20/03/2014 and invoiced on 28/03/2014 -----> i dont want to show it.

rec_date < vSelectedDate and inv_date < vvSelectedDate