Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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 (5)
1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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

Highlighted
Creator
Creator

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

 

 

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

 

Highlighted
Creator
Creator

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

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

Highlighted
Creator
Creator

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

 

Highlighted
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

Highlighted
Creator
Creator

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

😕

Highlighted
Partner
Partner

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