Minimum Date In Chart

[#1]

The age old question - how can I get the minimum date to use in an expression in a graph with a date dimension. Using set analysis only gives you the minimum of the date for the entire selection, however I need the minimum date for the time dimension of the graph, i.e. the graph has a time dimension of month, so for every month's bar in the graph, the minimum date should be the first of the month.

I need to get the opening customer count and this is the wrong expression:

sum( {< Period_date = {$(=min(Period_date)} >} cust_count)

[Anonymous]

try this?

=sum( {< Period_date = {'$(=Date(min(Period_date),'DD/MMM/YYYY'))'} >} cust_count)

Make sure Format of Period_date should be 'DD/MMM/YYYY'

[Anonymous]

By using the peek function to get the min date and by using the set analysis to get the min date  vaules

[MayilVahanan]

Hi

If you are using Date dimension, then try like this

Create a variable like vMinPeriodDate = Min(Period_Date)

After that, use that variable in ur set analysis

=sum( {< Period_date = {'$(=Date($(vMinPeriodDate) ,'DD/MMM/YYYY'))'} >} cust_count)

Hope it helps

[luciancotea]

Set analysis is calculated ONCE, per chart, not for every value.

[#6]

That's the problem. If set analysis was calculated for every value in the chart it would have worked.

[jagan · Accepted solution]

Hi,

Set analysis is calculated once per chart, you have use Aggr() like below

Sum(Aggr(If(Period_date = Min(TOTAL <Month> Period_date),  sum( cust_count)), Month, Period_date))

Note: Replace Month with your actual field name.

Hope this helps you.

Regards,

jagan.

[Anonymous]

Jagan, You rock man. Great answer. Thanks.