Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Set Analysis - Dates greater than a given number of days ago

Hi,

I'm running into a problem creating a set analysis using Today() and a user-specified variable.  I'd like the user to be able to interactively set a number of days and have the total cost of sales between that many days ago and today be summed i.e. sum all the sales from 10 days ago up until now.  So far I've come up with the following expression but it always yields zero:

=Sum({$<SalesDate={">=$(Date(Today()-$(vDays)))"}>}Cost)

Can anyone help point out the error in my expression?

Thanks,

Rav

1 Solution

Accepted Solutions
Sokkorn
Honored Contributor

Re: Set Analysis - Dates greater than a given number of days ago

Hi Rav,

I would suggest you to create a new field for set analysis (easy to control). Something like

[Table]:

LOAD

...

Num(SalesDate)    AS [SalesDateNum],

...

Then use =Sum({$<SalesDateNum = {">=$(=Today()-vDays)"}>} Cost) in your Text Object.

Regards,

Sokkorn

4 Replies
Sokkorn
Honored Contributor

Re: Set Analysis - Dates greater than a given number of days ago

Hi Rav,

I would suggest you to create a new field for set analysis (easy to control). Something like

[Table]:

LOAD

...

Num(SalesDate)    AS [SalesDateNum],

...

Then use =Sum({$<SalesDateNum = {">=$(=Today()-vDays)"}>} Cost) in your Text Object.

Regards,

Sokkorn

daveamz01
Contributor III

Re: Set Analysis - Dates greater than a given number of days ago

Hi Rav,

Sokkorn is right, you have to create a new field where the date will be in numeric format. For your data model I suggest to use: num(date#(Date,'M/D/YYYY h:mm')) as SalesDate, since your data format is not in the standard 'M/D/YYYY' or 'M/D/YYYY h:mm:ss[.fff] TT' format.

Then create another variable like vIniatialDate =today()-$(vDays) (easyer to update) then:

=Sum({<SalesDate={">=$(vInitialDate)"}>}Cost)

Regards,

David

Not applicable

Re: Set Analysis - Dates greater than a given number of days ago

Excellent, thank you Sokkorn!

-Rav

Not applicable

Re: Set Analysis - Dates greater than a given number of days ago

Thank you Dave for the additional advice.  Adding the variable for the initial date helps keep the set expression more readable.  There was a typo in the code example that defines the variable (just in case anyone ever tries copying and pasting from this thread):

vInitialDate =today()-$(vDays)

-Rav

Community Browser