YTD with previous month(s)

[a272070]

Hello,

I am trying to get this year to date function to work in Qlikview chart. As you can see below, I was able to do it in Excel. The YTD (Orange column) is the result each month. It is taking Green/Blue to get the H column. 

Anyway, I am looking to find a way to do the blue column (I) in Qlikview so that I can end up with the column H. Does anyone know how to do it? It is basically taking the current month + previous month(s) / current month + previous month(s). Thank you in advance!

[Andrei_Cusnir]

Hello,

Please try the following steps:

1. The demo dataset that I have looks like that:
2. 
3. Create a new Straight table with the following dimensions:
4. 
5. Then add a calculated dimension:

=Aggr(
    RangeSum(
    Above( 
        TOTAL 
        Sum([Yes Cases]),0,12)), [Yes Cases],Date) / Aggr(RangeSum( Above( TOTAL Sum([Total Cases by Month]),0,12)
    ),
    [Total Cases by Month],
    Date
)

This is the result:

As you can see the last column has the exact values as your YTD column. 

I hope that this information was helpful! In case it has helped you resolve the issue, please mark it as solution to allow other community members find this answer as well! 

[a272070]

I see that it works, but I am trying to show this in a graphical form. Do you know how I can do this formula but show in a bar chart by month? 

Thank you!

[a272070]

An issue I am facing is that the dashboard includes data from past years as well so I need this formula to only be on the current year. 

I have tried this set analysis within the expression, but I believe it is wrong because of using the "12". I have a YTD flag where for each month that is in 2021, it will show a "1". I just now need to find a way to not go back 12 months, but instead I need to go back to year start and the rest of the months after that to sum.

=Aggr(
RangeSum(
Above(
TOTAL
Sum({1<[Calendar YTD Flag] = {"(1)"}>}
[2.Yes Cases By Month]),0,12)), [2.Yes Cases By Month],CalendarDate)
/
Aggr(RangeSum( Above( TOTAL Sum({1<[Calendar YTD Flag] = {"(1)"}>}
[2.Total Cases By Month]),0,12)
),
[2.Total Cases By Month],
CalendarDate
)

[Andrei_Cusnir · Accepted solution]

Hello,

Here is my approach to the new issues that you have mentioned:

1. To address the issue with going back 12 months and make it so you go back only until the beginning of the year. My new expression is:

=Aggr(
    RangeSum(
    Above( 
        TOTAL 
        Sum([Yes Cases]),0,Num(Month(Date)))), [Yes Cases],Date) / Aggr(RangeSum( Above( TOTAL Sum([Total Cases by Month]),0,Num(Month(Date)))
    ),
    [Total Cases by Month],
    Date
)

I have replaced the number 12 with Num(Month(Date)). This expression will give you always the number of the month that you are evaluating. So in October it will be 10 and it will go back 10 steps, for August it will be 8, so it will go back 8 steps. This means that instead of going always 12 steps back, it will go back accordingly to the number of the month. You can modify this part as you like for different user case scenarios.

2. The second issue is to display only the dates for the current year. Here you will have to use a set analysis as you have correctly stated. I have tried the following set analysis:

{<Date={"*$(=Right(Year(Today()), 2))"}>}

This set analysis basically states that you will make the calculations where Date field is "*21". Therefore it will take all the dates, such as "1/1/2021" , "2/1/2021" etc. Instead of using the hard coded string "21", I have used the expression "Right(Year(Today()), 2)", which will take the date as of today, will extract only the year and then will take 2 characters from the end. This will leave us with "21", "22", "23" etc., depending on the year that we currently have.

So finally, I have created a Bar chart, with Date as dimension and expression:

=Aggr(
    RangeSum(
    Above( 
        TOTAL 
        Sum({<Date={"*$(=Right(Year(Today()), 2))"}>} [Yes Cases]),0,Num(Month(Date)))), [Yes Cases],Date) / Aggr(RangeSum( Above( TOTAL Sum({<Date={"*$(=Right(Year(Today()), 2))"}>} [Total Cases by Month]),0,Num(Month(Date)))
    ),
    [Total Cases by Month],
    Date
)

The entire expression is the same and the only changes are the orange part that is the set analysis and the purple part, which is the steps to calculate backwards. 

This is the new dataset that I have:

As you can see, I have also dates of previous years.

And this is the Bar chart that I get:

As you can see:

• It only shows the dates of current year
• For October the value is 0.989837..., for July is 0.9875, for March is 0.9875, for January is 1 etc. Those values correspond with the ones that you have in your table

I hope that this information was helpful. In case the answers have helped you resolve the issues, please mark them as solutions, so other community members could also find them easily!

[a272070]

Hi Andrei,

I really appreciate your help! This is great, but could you please attach your QVW you are using? It is really not working for me because I guess it may have something to do with my Master Calendar that I have is not on the "Date" level for this particular dataset, I mainly just have Month/year combination. It is strange with format of dates I have.. I think it would help if I can see the QVW you have so I can try to match it with mine as well.

Thanks in advance!

[Andrei_Cusnir]

Hello,

Here is my qvw file attached. I hope that it will help you resolve the issue on your side 😊