topic Re: Filter with Date dynamically in qlikview in QlikView

Filter with Date dynamically in qlikview

tripatirao — Wed, 25 Nov 2020 16:16:04 GMT

=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

Re: Filter with Date dynamically in qlikview

Anil_Babu_Samineni — Mon, 03 Apr 2017 06:55:13 GMT

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.

Re: Filter with Date dynamically in qlikview

vinieme12 — Mon, 03 Apr 2017 06:57:21 GMT

Dates in Set Analysis

Re: Filter with Date dynamically in qlikview

tripatirao — Mon, 03 Apr 2017 07:06:11 GMT

Hi anil

thanks for quick reply.

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

Regards

Tripati

Re: Filter with Date dynamically in qlikview

Anil_Babu_Samineni — Mon, 03 Apr 2017 09:21:12 GMT

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])