Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Set Expression with Date Island

Hi All,

I have multiple tables with a created Date/Calendar island.

In my Date Island script, I have added :

*,
If( DayNumberOfYear(Date) <= DayNumberOfYear(Today()), 1, 0 ) as IsInYTD
;

On the front-end of the app, I am trying to create a master item to have the YTD sales:

The formula I have without the year condition is the following: sum({\$<IsInYTD = {1}>}[Invoiced in GC])

Now, I don' t  know how to include the date condition which would be [Clearing date]=Date .

I have tried this way it but does not work: sum({\$<[Clearing date]=Date , IsInYTD = {1}>}[Invoiced in GC])

I have also tried with this formula: if([Clearing date]=Date,sum({\$<IsInYTD = {1}>}[Invoiced in GC])) but then my bar chart gives me an error message "the line chart is not dislpayed because if containes only undefined values".

Any idea how to solve this?

In my line chart, I am using the year and month field of my Calendar Island.

Labels (4)

• ### Variables

13 Replies
Specialist II

I think you'll want to do this...

sum({\$<[Clearing date]=P({<IsInYTD = {1}>}Date) >}   [Invoiced in GC])

May be like this -

sum({\$<SomeIDField = {"=([Clearing date]=Date)"} , IsInYTD = {1}>}[Invoiced in GC])

Contributor III
Author

Thanks for your reply Steve. I have tried but it gives me a straight horizontal line at a x value 😕

Contributor III
Author

Thanks for your reply, but does not work. basically gives me the same graph as if I was using the IF formula.

It gives me a graph from January until March only. See picture attached.

Are these dates in the same table?  are they having exactly same format? Do you have timestamp or decimal value in your date? probably a sample app can help to try few things

Contributor III
Author

Attached you will see a sample.

The clearing date is not in the same table as the [Invoiced in GC]).

But even If I use a measure from the same table, it gives me the same funny graph

Specialist II

Why wouldn't your chart dimensions be the Clearing date field (month and year of)?

Then sum({\$<[Clearing date]=P({<IsInYTD = {1}>}Date) >}   [Invoiced in GC]) would work.

Somehow clearning date has to associate with date to make it work. didn't realize both dates are not connected. In my expression 'SomeIDField' I meant with any ID field of your data model. By any chance you can rename Clearing date as Date in the CasePaymentStatistic table? and use Link.PST.SEQ in place of SomeIDfield.

Contributor III
Author

yeah I did try that already but gives me the same error message "contains undefined value

thanks for the help though