chart help.

[userid128223]

Hi Everyone

I have a chart with usual selection.

Company name

Sales person

Month

Year

Quarter

I have a calculated field called TimesRemaining.

=Sum({$<BILLABLE = {'T'}, TimePurchaseDate = {'>=$(=ADDMONTHS(TODAY(),-18))'}>}TimePurchasedHours) -  Sum({$<BILLABLE = {'T'}, ServiceDate = {'>=$(=ADDMONTHS(TODAY(),-18))'}>}HoursWorked)

I have 2 charts

Full history regardless of selection: this charts works fine. (here I disable all the selection in set analysis, hence my TimeRemaining is calculated for all period for that company)

However when I try to do history by month (now I allow user to select time like day, month, quarter)

The TimeRemaing part show only that month instead of showing for entire period.

I am confused on how to get this to work.

[Gysbert_Wassenaar]

If you want to ignore your selections in your expression you can change it to:

=Sum({1<BILLABLE = {'T'}, TimePurchaseDate = {'>=$(=ADDMONTHS(TODAY(),-18))'}>}TimePurchasedHours) -  Sum({1<BILLABLE = {'T'}, ServiceDate = {'>=$(=ADDMONTHS(TODAY(),-18))'}>}HoursWorked)

Maybe you want to change today() to max(TimePurchaseDate) and max(ServiceDate) so you the 18 months period ends with the maximum TimePurchaseDate and ServiceDate of the day, month or quarter of your selections.

If you don't want to ignore all selections, you'll have to add the fields that influence the selections in TimePurchaseDate and ServiceDate to the expression

=Sum({$<BILLABLE = {'T'}, TimePurchaseDate = {'>=$(=ADDMONTHS(TODAY(),-18))'}, Year=,Quarter=,Month=>}TimePurchasedHours) - Sum({$<BILLABLE = {'T'}, ServiceDate = {'>=$(=ADDMONTHS(TODAY(),-18))'}, Year=,Quarter=,Month=>}HoursWorked)

Change Year, Quarter and Month to the respective names of your fields.

[userid128223]

Hi Gysbert

attach are 2 images of snapshot of 2 charts

In this chart I have allowed user to select months and year, hence the chart is adjusted as per selection. But you see the TimeRemaining as -10 that is incorrect, because it is only showing me for that month user has selected, I want the time remaining to be total of all the word for that company.

This below chart. I have disallowed user to do any selected under month or year. hence TimeRemaining comes out right.

[userid128223]

how can i get this formula to work so that it will have no impact of time selection. however will do total per company.

in chart, it gets adjusted when i select time.

=Sum({$<BILLABLE = {'T'}, TimePurchaseDate = {'>=$(=ADDMONTHS(TODAY(),-18))'}>}TimePurchasedHours) -  Sum({$<BILLABLE = {'T'}, ServiceDate = {'>=$(=ADDMONTHS(TODAY(),-18))'}>}HoursWorked)

[userid128223]

taking 1 instead of $ wont help because i dont want entire data set, i want entire data set per company.

[Anonymous]

Hi,

  Can You please provide sample applictaion or data.

Regards

[userid128223]

it will be hard for me to do that. sensitive data.

[Anonymous]

Hi,

   i am asking just sample data,not your data

[userid128223]

i understand but how to do i replicate the data set with a fake one.