Conditional dimension/expression based on variable

[jduarte12]

Hello,

I have a line chart which content relies on a date selection. For the date selction, I have created Calendar objets (Start and End date) linked to date variables.

However, I need this chart to display hourly information for recent dates (the default selection in the calendar), and daily aggregation for older dates and and wider time intervals. My idea is to have a calculated dimension:

if(date(startDate)>=date($(thresholdDate)),date(hourlyDate,'D/MM/YY hh:mm:ss'),date(dailyDate,'D/MM/YY'))

that makes my chart assume a certain dimension regarding the date selection made in the calendar. The expression also considers the values associated with each dimension:

=if(date(startDate)>=$(thresholdDate),num(sum({<hourlyDate={">=$(=date(startDate))<=$(=date(endDate))"}>}hourlyValues),'#.###'),

num(sum({<dailyDate={">=$(=date(startDate))<=$(=date(endDate))"}>}dailyValues),'#.###')).

However, the chart is displaying a "No numeric valus on x-axis" message. I guess the solution for conditionally matching hourly/daily dates (dimensions) and values (expressions) is quite easy... Am I missing something?

Thank you & Best regards,

João Duarte

[Kushal_Chawda]

try below

if(date(startDate)>='$(thresholdDate)',num(sum({<hourlyDate={">=$(=date(min(startDate)))<=$(=date(max(endDate)))"}>}hourlyValues),'#.###'),

num(sum({<dailyDate={">=$(=date(min(startDate)))<=$(=date(max(endDate)))"}>}dailyValues),'#.###'))

[jduarte12]

Thanks, but it keeps displaying "no numerica values" message.

[sasiparupudi1]

I think there is a mix up here between dates and date times.

will you be able to share a sample?

What o/p youi get  when you put the following g exp in a text box

num(sum({<hourlyDate={">=$(=date(startDate))<=$(=date(endDate))"}>}hourlyValues),'#.###')

the format of hourlydate and date(startDate) and date(endDate) should be identical otherwise, you wont get any results back

hth

Sasi

[jduarte12]

The point is that I need to have one date-time dimension for the hourly values and a date dimension for daily values.

The date variables are in the same date format as the daily values... Maybe an option would be putting everything in numerical format (e g: 42368), in order to cope with both date and date-time.

[sunny_talwar]

Would you be able to share a sample?

[jduarte12 · Accepted solution]

I managed to solve the situation by creating a new variable for the threshold date value.

The one I was using was related with the selected start date, which was not appropriate.

Thank you all for the help,

João Duarte