Limiting the date as a calculated dimension

[#1]

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

[#2]

Hi Tim,

Thanks for your response. But with this dimension I don't have any dates listed as a dimensions in my chart. I'd like to see last 10 days on X and count of operations on Y per each day.

Do you have any ideas how I could approach this?

[Anonymous · Accepted solution]

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.

[jagan]

Hi,

Try like this

LOAD

*,

date(floor(EFFECTIVEDATE)) as DATE

FROM DataSource;

Count({<DATE={'>=$(=Date(Max(DATE) - 9))<=$(=Date(Max(DATE)))'}>} [ITRNKEY])

Regards,

Jagan.