Filter with Date dynamically in qlikview

[tripatirao]

=pick(Dim_No1,

'Opening Stock',

'Order',

)

For Apr 2016 My expression is given below

=pick(Dim_No1,

(//Opening Stock

(sum({<[Item Ledger Entry.Item Type]={2},[Item Ledger Entry.Posting Date]={'<=31-03-2016'}>}[Item Ledger Entry.Quantity]))

),

//Order

(sum({<[Purchase_Line.Document Type]={1},[Item Ledger Entry.Item Type]={2},[Purchase_Line.PurchaseOrderDate]={'>=01-04-2016<=30-04-2016'}>}[Purchase_Line.Quantity])),

I have master calender date that is linked with all transction date like [Item Ledger Entry.Posting Date],[Purchase_Line.PurchaseOrderDate]

for  expression opening stock for apr 2016  the data should be filtered with starting date of master calender date upto march 31st 2016

similarly for may 2016 the data shold be filtered starting date of master calender date upto  Apr 30th 2016

For expression Order the for apr 2016  the data should be filtered 1st April 2016 to 30th apr 2016

for may 2016 the data should be filtered with 1st May 2016 to 31st May 2016

Please help me with set analysis expression for date range selection.

I have attached the output of the expression which i am getting with hardcoding the date value.

Regards

Tripati

[Anil_Babu_Samineni]

I have master calender date that is linked with all transction date like [Item Ledger Entry.Posting Date],[Purchase_Line.PurchaseOrderDate]

From here, Do you have single calendar or multiple. If single calendar, What was the calendar date field name and then which is Fact date field.

[vinieme12]

Dates in Set Analysis

[tripatirao]

Hi anil

thanks for quick reply.

have  single master calender and the date field is IncCalender Date.

Regards

Tripati

[Anil_Babu_Samineni]

Would you provide application, please to check whether it works or not

Here, you go sample expression to get idea and it helps to you

Sum({<[Purchase_Line.Document Type]={1},[Item Ledger Entry.Item Type]={2}, Calendardate = {">= $(=Date(Min(CalendatDate),'DD-MM-YYYY')) <= $(=AddMonths(MonthEnd(Date(Max(CalendarDate),'DD-MM-YYYY')),-1))"}>} [Purchase_Line.Quantity])