Using Set Analysis to Show Data from a Range of Dates

[MTS95]

Hi there,

I am trying to create a KPI that shows the revenue a department accrued between certain dates. The dates would line up with the Quarters of a Fiscal Year:

Quarter 1	July 1st through September 30th
Quarter 2	October 1st through December 31st
Quarter 3	January 1st through March 31st
Quarter 4	April 1st through June 30th

I have a filter pane that allows users to select the Quarter number ([sales.QuarterNumber]) and a field that tells me exactly when each customer transaction occurred, with values like 2/21/2012 8:56:02 AM ([sales.TransDate]). I was worried this value might be recognized as a string, so I created a new field called [sales.TransDate_DateFormatted] which I made by using the following formula in the data load:

Date#(Date(TransDate, 'DD-MM-YYYY'), 'YYYY-MM-DD')

In the Qlik Sheet, I have the following expression in the KPI:

=if(

    GetSelectedCount([sales.QuarterNumber]) = 0,

        sum( {<[sales.TransDate_DateFormatted] = {'>=$(=Date(QuarterStart(Today()))) <=$(=Date(Today()))'}>}

[sales.TotalRevenue]))

I'd like to make the KPI display the current Quarter's revenue if no Quarter selections are made. Before I go further and add more to the if statement to account for each value of the field [sales.QuarterNumber], I noticed that this expression only returns the value $0.00, which is incorrect.

Would someone be able to clarify what is wrong with this expression? I modeled it after the solution in this post. Any feedback is greatly appreciated.

I am hoping to make the full completed expression for the KPI look something like this, so if someone spots an issue with it, feel free to let me know:

=if(

    GetSelectedCount([sales.QuarterNumber]) = 0,

        sum( {<[sales.TransDate_DateFormatted] = {'>=$(=Date(QuarterStart(Today()))) <=$(=Date(Today()))'}>}

[sales.TotalRevenue]),

if(

    [sales.QuarterNumber]) = 'Quarter 1',

        sum( {<[sales.TransDate_DateFormatted] = {'>=2012-07-01 <=2012-09-30'}>}

[sales.TotalRevenue]),

if(

    [sales.QuarterNumber]) = 'Quarter 2',

        sum( {<[sales.TransDate_DateFormatted] = {'>=2012-10-01 <=2012-12-31'}>}

[sales.TotalRevenue]),

if(

    [sales.QuarterNumber]) = 'Quarter 3',

        sum( {<[sales.TransDate_DateFormatted] = {'>=2013-01-01 <=2013-03-31'}>}

[sales.TotalRevenue]),

if(

    [sales.QuarterNumber]) = 'Quarter 4',

        sum( {<[sales.TransDate_DateFormatted] = {'>=2013-04-01 <=2013-06-30'}>}

[sales.TotalRevenue]),

)))))

[RafaelBarrios · Accepted solution]

HI @MTS95 

First, when you need to do an evaluation like <,>,<=,>= inside { } you have to use {" "} and not {' '}

Second, i would check how you new date field is created

Date#() this is to especify how date is received

Date() this is to especify how you what to change it

so, as i think your app date format is YYYY-MM-DD and you are getting MM-DD-YYYY hh:mm:ss, in script i would do:

Date(Date#(TransDate, 'DD-MM-YYYY hh:mm:ss'), 'YYYY-MM-DD') as [sales.TransDate_DateFormatted]

date(Date#(TransDate, 'DD-MM-YYYY hh:mm:ss') as [sales.QuarterNumber]

Third, as [sales.QuarterNumber] is a field of your sales table you shouldn't need all those conditions, only when you dont have any selection

=if(

    GetSelectedCount([sales.QuarterNumber]) = 0,

        sum( {<[sales.TransDate_DateFormatted] = {">=$(=Date(QuarterStart(Today()))) <=$(=Date(Today()))"}>}

[sales.TotalRevenue]),

    sum([sales.TotalRevenue])     //Else - as you are selecting the quarter

)

hope this works for you.

Best,

[vinieme12]

Date#() is for interpretation

Date() is for formatting

This does not take out the time part from the timestamp,  for which you need to floor the timestamp field

also     2/21/2012 8:56:02 AM   =  M/DD/YYYY h:mm:ss TT

Just Make below changes in Script

1)        SET DateFormat='YYYY-MM-DD';

2)        Date(floor(Date#(TransDate, 'M/DD/YYYY h:mm:ss TT'))) as [sales.TransDate_DateFormatted]