Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
roisolberg
Creator
Creator

YTD calculations not working with variable

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 )

clipboard_image_0.png

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:

clipboard_image_1.png

 

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!

 

 

 

 

Labels (3)
1 Solution

Accepted Solutions
gmenoutis
Partner - Creator II
Partner - Creator II

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.

View solution in original post

11 Replies
Shubham_Deshmukh
Specialist
Specialist

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
Creator
Creator
Author

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:

clipboard_image_1.png

 

 

Shubham_Deshmukh
Specialist
Specialist

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
Creator
Creator
Author

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

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)

bar.png

Let me know.

roisolberg
Creator
Creator
Author

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

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
Creator
Creator
Author

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
Partner - Creator II
Partner - Creator II

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.