topic Re: YTD calculations not working with variable in Visualization and Usability

YTD calculations not working with variable

roisolberg — Sat, 16 Nov 2024 02:42:39 GMT

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!

Re: YTD calculations not working with variable

Shubham_Deshmukh — Mon, 19 Aug 2019 08:31:46 GMT

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

Re: YTD calculations not working with variable

roisolberg — Mon, 19 Aug 2019 08:42:37 GMT

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:

Re: YTD calculations not working with variable

Shubham_Deshmukh — Mon, 19 Aug 2019 09:04:43 GMT

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)

Re: YTD calculations not working with variable

roisolberg — Mon, 19 Aug 2019 09:35:09 GMT

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?)

Re: YTD calculations not working with variable

Shubham_Deshmukh — Mon, 19 Aug 2019 10:46:08 GMT

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.

Re: YTD calculations not working with variable

roisolberg — Mon, 19 Aug 2019 11:02:12 GMT

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..

Re: YTD calculations not working with variable

Shubham_Deshmukh — Mon, 19 Aug 2019 11:10:00 GMT

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

Re: YTD calculations not working with variable

roisolberg — Mon, 19 Aug 2019 11:43:02 GMT

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...

😕

Re: YTD calculations not working with variable

gmenoutis — Mon, 19 Aug 2019 11:58:59 GMT

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.

Re: YTD calculations not working with variable

roisolberg — Mon, 19 Aug 2019 12:10:57 GMT

wow,

thank you very much for a very good and explained solution!!

this was very helpful both to my model and my knowledge. 🙂

Re: YTD calculations not working with variable

Shubham_Deshmukh — Mon, 19 Aug 2019 12:34:27 GMT

Hi @gmenoutis ,

That' great knowledge sharing.

Can u pls put some light on my revert, why it is not working in @roisolberg 's case?

-Shubham