13 Replies Latest reply: Jun 6, 2016 1:28 PM by Danilo Torres

# Date Problem

Hi guys, my problem is:

• I have two tables: CLIENT and CALENDAR
• They are joined by YEAR_MONTH
• CLIENTE has a column called: INCLUSION_DATE
• In CALENDAR i have YEAR_MONTH, YEAR, MONTH, DAY, DATE

I would like to select YEAR=2016, MONTH=MAR and create a line chart that give me how many CLIENTs i have day by day of the whole selected month, from the day 1 to the 30.

My set analisys was:

count(

{

<INCLUSION_DATE={'<=\$(=DATE)'}>

}

CLIENT)

It works when i select just one DATE. But when i select the month the chart show me nothing.

My expectation is someting like this:

• ###### Re: Date Problem

If you have already selected Year and Month, I don't think you would need Set analysis:

Count(CLIENT)

• ###### Re: Date Problem

No... CLIENT and CALENDAR are joined by YEAR_MONTH.

With month selected and count(CLIENT) my chart will show:

• DAY from CALENDAR as Dimension: the same count value all days from the month
• DAY extracted from INCLUSION_DATE as Dimension: how many CLIENT was added in each day.

And what i want is: how many CLIENT i have until each day.

• ###### Re: Date Problem

count(

{

<INCLUSION_DATE={"<=\$(=max(DATE))"}, Month=>

}

CLIENT)

• ###### Re: Date Problem

Putting "Month=" the chart shows all dates from selected year.

Putting max(DATE) the chart shows, for all dates, the same value, the value of the max date: 31/12/2016

• ###### Re: Date Problem

count(

{

<INCLUSION_DATE={'<=\$(=Max(DATE))'}>

}

CLIENT)

• ###### Re: Date Problem

Putting max(DATE) the chart shows, for all dates, the same value, the value of the max date: 31/03/2016

• ###### Re: Date Problem

Thanks!

• ###### Re: Date Problem

thats my app!

Thanks to all for your support!

• ###### Re: Date Problem

Can you check your sample. The YEAR_MONTH and INCLUSION_DATE are not aligning up properly here

• ###### Re: Date Problem

Thanks sunny.

These two fields represent different things:

• INCLUSION_DATE: Represents the date my Client accepted my promotion (another table that doesn't matter for now)
• YEAR_MONTH: Represents the CLIENT register datas on YEAR and MONTH selected.
• ###### Re: Date Problem

Your uploaded document shows something else than what you mentioned in your OP. A flat line from day one. This is to be expected as the granularity of your key connectiing Calendar to Client is at the month level, meaning that it produces the same set of clients for every day in a month. Can you add the actual date to the key and let the calendar decide on what granularity to use?

Also from your original picture it seems like you want to accumulate the number of clients. Take that into account when setting the expression properties.

Best,

Peter

• ###### Re: Date Problem

Also keep in mind that set analysis produces a reduced/expanded data set before the object/chart is recalculated and not a different set row-by-row in your table or chart. This accounts for your initial problem whereby the chart works for a single date (field DATE in set analysis returns a single value) and not for any other selection (DATE returns multiple values which translates to NULL in the \$-substitution expression). Result: no INCLUSION_DATE matches the condition.

Best,

Peter

• ###### Re: Date Problem

Yeah Peter! Thanks for your attention and response.

And thats the point, the granularity. But as this change will take me a hard work (my app is not only that a sent. That are a lot of others tables in my cloud). So changing this join from CLIENT to CALENDAR will broken all of my app.

Thats the reason i'm trying to implement that vision by day using set analisys.

Thanks