How to define an aggregation dependent on a date being in the prior year, between January 1 and this date (using YearToDate()).

[plexpro52]

I would like to compare this year's sales YTD to the same time period from last year.  I have tried this formula, and get nothing:

Sum({$<InvoiceYear={$(vCurrentYear)}>}ItemSalesAmt)

/ Sum({$<InvoiceDate={"$(=YearToDate(InvoiceDate,-1))"}>}ItemSalesAmt)

I have tried this formula:

Sum({$<InvoiceYear={$(vCurrentYear)}>}ItemSalesAmt)

/ Sum({$<"$($(YearToDate(InvoiceDate,-1)))">}ItemSalesAmt)

With this latter formula the divisor resolves to the same value as the dividend, resulting in a ratio of 1 (or 100%).

How do I actually filter the aggregation to select dates belonging to the prior year, between January 1 and the current date?

[sunny_talwar]

Is this in a chart where you might have InvoiceDate as a dimension? If this is true than the mentioned set analysis won't work and you might have to use RangeSum(Above()) function. Need more details to guide you any better.

Best,

Sunny

[Anonymous]

See function InYearToDate().  For the previous year, it will be

sum(if(InYearToDate(InvoiceDate,ReloadTime(),-1)),ItemSalesAmt)

You can of course replace the ReloadTime() with today() or better with a variable so you can use any date as "current date".

[plexpro52]

Hello Sunny,

The dimension is Customer Name, and the measure is defined by second formula that I indicated above.  Here is a picture of the bar chart:

Actually, as you can see by the preview at the bottom, my comment about the formula always resolving to 100% was incorrect.  The expression Sum({$<"$($(YearToDate(InvoiceDate,-1)))">}ItemSalesAmt) apparently resolves to at most the value of the numerator as calculated by the expression Sum({$<InvoiceYear={$(vCurrentYear)}>}ItemSalesAmt).  It is extremely unlikely that several dozen customers had exactly the same sales for the time period for each year, and probably unlikely that absolutely no customer would have sold less in the current year than the last year (although given our sales growth, it might be possible).

I am at a loss to understand what it is calculating, or how to attain the desired result.

[sunny_talwar]

Try this may be:

=Sum({<InvoiceYear={$(vCurrentYear)}>}ItemSalesAmt)/Sum({<InvoiceDate={"$(='>=' & Date(AddYears(YearStart(Max(InvoiceDate)), -1)) & '<=' & Date(AddYears(Max(InvoiceDate), -1)))"}>}ItemSalesAmt)

Where this in a text box ='>=' & Date(AddYears(YearStart(Max(InvoiceDate)), -1)) & '<=' & Date(AddYears(Max(InvoiceDate), -1)) should give you the YTD Date range in the same format as InvoiceDate for last year YTD.

HTH

Best,

Sunny

[plexpro52]

Hello Michael,

>> See function InYearToDate().

Actually, my date is not a timestamp, so according to my understanding, the YearToDate() is the function that I would want to use.  I did try it as you suggested, but it also did not work.

I also tried your suggestion, changing your code to use YearToDate() as I originally specified, with the preceding if(), but that did not work either, I presume in part because if() has three required parameters, not one.

So I tried Sum(if(YearToDate(InvoiceDate,-1),ItemSalesAmt,0)) for the denominator, and it appeared to produce reasonable results; I will have to verify the numbers...

[plexpro52]

Hi Sunny,

I am trying Sum(if(YearToDate(InvoiceDate,-1),ItemSalesAmt,0)) for the denominator, and it appeared to produce reasonable results; I will have to verify the numbers...

If it works, it would be preferable for its simplicity; if not, I will give your suggestion a try.

Thanks!

René

[sunny_talwar]

Sounds good

[Anonymous]

I misplaced the ')'...

If() function has two required parameters, but I'm adding 0 if it helps:

sum(if(InYearToDate(InvoiceDate,ReloadTime(),-1),ItemSalesAmt, 0))

I assume still that InvoiceDate is a date field (no need to be timestamp).

[plexpro52]

Hi Michael,

>> I misplaced the ')'...

I did notice that it was close...

>> If() function has two required parameters...

The documentation online doesn't indicate any optionality for if() parameters, but I didn't verify it.

Regarding InvoiceDate, yes it is a mere date, for which reason I was using the YearToDate() function.

When I verify the results, I'll post a reply.  Thanks!

René