Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Limiting the date as a calculated dimension

Good day!

I have a transaction table in DB and load data from it. In this table all the transactions have its own timestamps (EFFECTIVEDATE) and types (SOURCEKEY). I group them by date using DATE(FLOOR(EFFECTIVEDATE)) function and use this field as a dimension.

Could you please help me to limit dimension by last 10 days and show them in the bar chart?

Regards,

Denis

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

OK, I've had a bit of time to look at this in more depth.

The problem stems from the fact you want to aggregate by date, but have a timestamp value.

In the load script, you can use:

date(floor(EFFECTIVEDATE)) as EFFECTIVEDATE

Which will load EFFECTIVEDATE as a date only and strip the time component out.

This will then mean you can use this as a set analysis modifier in the application.

So in the chart your dimension will be: EFFECTIVEDATE

and you measure will be: Count({$<EFFECTIVEDATE={'>$(=date(max(EFFECTIVEDATE)-10))'}>}[ITRNKEY])

I exported your data into Excel and re-loaded it, and I've attached my qvf to show you how this works.

*Simple way*

If you don't want to edit the script, you could use a calculated dimension of date(floor(EFFECTIVEDATE)) and simply use dimension limits to show only the last 10 days.

View solution in original post

12 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this in expression

=Sum({EFFECTIVEDATE={'>=$(=Date(Max(EFFECTIVEDATE) - 9))<=$(=Date(Max(EFFECTIVEDATE)))'}>} SomeMeasureName)

Note: Replace SomeMeasureName with your actual field name you want to aggregate.

Regards,

Jagan.

Not applicable
Author

Hi,

I can't figure out why Qlik Sense says that there is an error in Expression: '}' expected. Could you please help me to investigate this.

Denis

jonathandienst
Partner - Champion III
Partner - Champion III

There is an opening  '<'  missing in Jagan's suggestion

=Sum({<EFFECTIVEDATE={'>=$(=Date(Max(EFFECTIVEDATE) - 9))<=$(=Date(Max(EFFECTIVEDATE)))'}>} SomeMeasureName)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Hi Jonathan,

Thanks for your correction. Syntax of your Expression is correct, but the whole string after submitting is red.

I think that the problem I still have that I load EFFECTIVEDATE from DB without any conversion, but it DB stores this field as DATE field.

P.S. I've cheched the data in EFFECTIVEDATE field in QS ad it's like '11.11.2014 13:25:03'

Do you have in mind any other suggestions why this string could be in red?

Denis

Not applicable
Author

Denis,

Try with " quote instead of ' quote and also $ is missing before Effectivedate.

use the below expression.

=Sum({$<EFFECTIVEDATE={">=$(=Date(Max(EFFECTIVEDATE) - 9)) <=$(=Date(Max(EFFECTIVEDATE)))"}>} SomeMeasureName)


Regards,

Sridhar.

Not applicable
Author

Sridhar,

Still field in red. Could you please take a look at my example I've attached here?

Denis

Not applicable
Author

Denis,

Unfortunately I Could not able to open .qvf extension.

Regards,

Sridhar.

Not applicable
Author

Sridhar,

This is a Qlik Sense application file format.

Denis

Anonymous
Not applicable
Author

Because you are using this calculation as a Dimension, you need to put the whole expression in a $ expansion.

So you dimension becomes:

$(=COUNT({$<EFFECTIVEDATE={">=$(=Date(Max(EFFECTIVEDATE) - 9)), <=$(=Date(Max(EFFECTIVEDATE)))"}>} ITRNKEY))