topic Re: Date Calculation in Set Analysis in QlikView

Date Calculation in Set Analysis

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

Dear Experts,

I want to calculate Sum of Sales for Previous Year Q2 which is 01-Apr-2017 to 30-Jun-2017.

In data source I have column OrderDateTime which is having date and time like "01/01/2017 11:57:00". From this column I have created a new column OrderDate which is having only dates in DD/MM/YYYY format.

For above requirement I tried following Set Analysis expression but I am getting 0 as output :

=Sum({<OrderDate = {">=Date(AddYears(QuarterStart(YearStart(Today()),1),-1),'DD/MM/YYYY') <= Date(MonthEnd(AddYears(QuarterEnd(YearStart(Today()),1),-1)),'DD/MM/YYYY') "}>}Sales)

Could you please help me in correcting this expression ?

Thanks & Regards,

Shantanu

Re: Date Calculation in Set Analysis

rubenmarin — Tue, 24 Apr 2018 09:10:08 GMT

Hi Shantanu, try adding a dollar-expansion to set analysis, also maybe needs to ignore other selected values like Year:

=Sum({<OrderDate = {">$(=Date(AddYears(QuarterStart(YearStart(Today()),1),-1),'DD/MM/YYYY'))<= $(=Date(MonthEnd(AddYears(QuarterEnd(YearStart(Today()),1),-1)),'DD/MM/YYYY'))"}, YearField>}Sales)

Using a numeric date field in set analysis can help to avoid format issues, the numeric field can be created as:

Num(OrderDate) as NumDate

Re: Date Calculation in Set Analysis

YoussefBelloum — Tue, 24 Apr 2018 09:14:54 GMT

Hi,

try this (you were missing the red parts):

=Sum({<OrderDate = {">=$(=Date(AddYears(QuarterStart(YearStart(Today()),1),-1),'DD/MM/YYYY')) <= $(=Date(MonthEnd(AddYears(QuarterEnd(YearStart(Today()),1),-1)),'DD/MM/YYYY'))"}>}Sales)

Re: Date Calculation in Set Analysis

shan_sriv — Tue, 24 Apr 2018 09:31:27 GMT

Thanks a lot for your help Ruben.

Re: Date Calculation in Set Analysis

rubenmarin — Tue, 24 Apr 2018 09:34:27 GMT

You are welcome.

Check if it should be:

=Sum({<OrderDate = {">=$(=Date(AddYears(QuarterStart(YearStart(Today()),1),-1),'DD/MM/YYYY'))<= $(=Date(MonthEnd(AddYears(QuarterEnd(YearStart(Today()),1),-1)),'DD/MM/YYYY'))"}, YearField>}Sales)

Re: Date Calculation in Set Analysis

YoussefBelloum — Tue, 24 Apr 2018 09:34:57 GMT

keep in mind that with this, DAY 01/04/2017 will not be counted, so try to verify if you need it and so add this > to Ruben's expression