YTD calculations not working with variable

[roisolberg]

Hi All,

First, i know there has been many posts and questions regarding this issue, but i haven't found a solution 

i am trying to make a YTD/LYTD calculations, that will count the number of opened projects in 2018 vs 2019

iv'e defined a master calendar and it is working well and in it i defined :

If( TempDate - YearStart(TempDate) <= Today() - YearStart(Today()), 1, 0 ) as IsInYTD

so far so good, now i tried doing a bar chart to compare 2019-2018 by month

so i defined the variables in the UI variables (Based on my calendar)

vMaxDate - Max(KickOffDate)

vMaxYear - Max(KickOffYear)

vPriorYear - Max(KickOffYear)-1

and than i tried to do the set expression that will count the number of projects in that time period,

when i do the set expression using {'2019'}  and  {'2018'}  it works, but when i use variables it makes the sum of 2018+2019

*CountProjects is defined in the project table '1 as CountProjects'

Count( {$<IsInYTD={1},KickOffYear={'2019'}>} CountProjects ) 

Count( {$<IsInYTD={1},KickOffYear={'2018'}>} CountProjects )

when i try to change it to the variable using this set:

Count( {$<IsInYTD={1},KickOffYear={'=$(vMaxYear)'}>} CountProjects ) - 2019

Count( {$<IsInYTD={1},KickOffYear={'=$(vPriorYear)'}>} CountProjects )  - 2018

i get the same value for both:

iv'e tried looking for this in older posts but no matter how i tried i didn't get the right calculation if any.

any ideas?

Thanks!

[Shubham_Deshmukh]

Hi,

First check whether correct values are coming in your variables or not.

If Yes, then try this

Count( {$<IsInYTD={1},KickOffYear={'$(=vMaxYear)'}>} CountProjects ) - 2019

Count( {$<IsInYTD={1},KickOffYear={'$(=vPriorYear)'}>} CountProjects )  - 2018

Rgds,

Shubham

[roisolberg]

Hi Shubham,

iv'e tried this as well but the outcome is 0..

You can see here that the 'MaxYear' and 'PriorYear' looks ok, 

the Last year i've kept without any changes and i changed the YTD as  per your replay

this is the outcome:

[Shubham_Deshmukh]

Hi ,

WHy you are not using normal way for YTD and LYTD? like this

YTD:

=Count({$<yourDate = {'>=$(=YearStart(Today())))'}>} yourQuantity)

LYTD:

=Count({$<yourDate = {'>=$(=Date(AddYears(YearStart(Today()),-1)))<$(=Date(AddYears(Today(),-1)))'}>} yourQuantity)

[roisolberg]

Hi Shubham,

i just tried doing it your way and i  get zero's in the calculation

am i missing something?

the kickoffdate is defined in the master calendar, should i create it differently? (for example in the original table?)

[Shubham_Deshmukh]

No Need to create any dates, I have normal orderDate in my data and I am using the  Same.

Below code is working me now, check this one,

Dimension:

MonthField created from date field in Master Calendar

YTD : 

=Sum({<OrderDate = {">=$(=YearStart(Today()))<=$(=Today())"}>}ShippingCost)

LYTD:

=Sum({<OrderDate = {">=$(=AddYears(YearStart(Today()),-1))<=$(=AddYears(Today(),-1))"}>}ShippingCost)

Let me know.

[roisolberg]

First of all thank you very much for your help! i really appreciate it.

The YTD is working well, but the LYTD gives me somehow 2018+2019

only when i subtract  YTD-LYTD i get an accurate LYTD..

[Shubham_Deshmukh]

Actually, you have to check the data because it is impossible what you are saying,

=Sum({<OrderDate = {">=$(=AddYears(YearStart(Today()),-1))<=$(=AddYears(Today(),-1))"}>}ShippingCost)

AddYears(YearStart(Today()),-1) ->  01-JAN-2018

AddYears(Today(),-1) -> 19-AUG-2018

So anyhow it will give you data between 01-JAN-2018 and 19-AUG-2018

[roisolberg]

i understand, yet it still gives me a count of 66 that is 30+36 (2018+2019)

if i filter by the year, i get the correct results, but without a filter it sums them both together...

😕

[gmenoutis · Accepted solution]

Let's debug your expression:

Count( {$<IsInYTD={1},KickOffYear={'=$(vMaxYear)'}>} CountProjects ) 

By using single quotes (') in your element list, you are telling QS to filter all KickOffYears which have the same text value with whatever is within the quotes. Your $(vMaxYear) will not be evaluated, it is seen as part of the string. None of your KickOffYears have a text value of =(vMaxYear), so the field selection fails. (I am not sure why this returns everything and not 0).

I can propose 2 solutions here. One is using a search mask, what is what I guess you wanted to do:

Count( {$<IsInYTD={1},KickOffYear={"$(vMaxYear)"}>} CountProjects ) 

Note the use of the double quotes: they initiate a search mode, and within search mode, formulas are evaluated.

However, I'd rather not force the engine to make a heavy search and instead just do a little more work and add an evaluation level to the whole formula:

$(='Count( {$<IsInYTD={1},KickOffYear={' & $(vMaxYear) & '}>} CountProjects )')

This way, the variable will be evaluated first, then its value will be inserted into the string (much like your own manual versions that work), and then the final evaluation will take place.