Solved: Re: Re: Limiting the date as a calculated dimensio... - Qlik Community

Report Inappropriate Content · ‎2014-11-12

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

Anonymous · ‎2014-11-13

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

jagan · ‎2014-11-12

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.

Report Inappropriate Content · ‎2014-11-13

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 · ‎2014-11-13

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

Report Inappropriate Content · ‎2014-11-13

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

Report Inappropriate Content · ‎2014-11-13

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.

Report Inappropriate Content · ‎2014-11-13

Sridhar,

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

Denis

Report Inappropriate Content · ‎2014-11-13

Denis,

Unfortunately I Could not able to open .qvf extension.

Regards,

Sridhar.

Report Inappropriate Content · ‎2014-11-13

Sridhar,

This is a Qlik Sense application file format.

Denis

Anonymous · ‎2014-11-13

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

Limiting the date as a calculated dimension