Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Tags (3)
1 Solution

Accepted Solutions
tap
Contributor

Re: Re: Limiting the date as a calculated dimension

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.

12 Replies
MVP
MVP

Re: Limiting the date as a calculated dimension

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

Re: Limiting the date as a calculated dimension

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

MVP
MVP

Re: Limiting the date as a calculated dimension

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

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

Not applicable

Re: Limiting the date as a calculated dimension

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

Re: Limiting the date as a calculated dimension

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

Re: Re: Limiting the date as a calculated dimension

Sridhar,

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

Denis

Not applicable

Re: Limiting the date as a calculated dimension

Denis,

Unfortunately I Could not able to open .qvf extension.

Regards,

Sridhar.

Not applicable

Re: Limiting the date as a calculated dimension

Sridhar,

This is a Qlik Sense application file format.

Denis

tap
Contributor

Re: Limiting the date as a calculated dimension

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))

Community Browser