Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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]),
)))))
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,
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,
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]