topic Re: Set Analysis Month & YTD in QlikView

Set Analysis Month & YTD

Fri, 24 Jan 2014 15:50:53 GMT

Hello,

I don't understand the current and last year flags in the master calendar and was wondering if I would be able to make a straight table that allows me to see a particular month and a different YTD (ie.Month 12 and SepYTD) using flags.

Currently I have written into my script the YTD's and how they match with each month ie. FebYTD = Month 1 & 2. However this has made it difficult when building my chart as it will only let me select a month that is linked with the particular YTD. I have also tried to use set analysis so that month will not be taken into account when calculating the YTD column but it makes no difference.

I have looked at lots of links on the community, but cant seem to find my answer.

Re: Set Analysis Month & YTD

Fri, 24 Jan 2014 15:55:36 GMT

Hi Anna,

try something like this

sum({<Year={$(=max(Year))}, YearMonthNum={"<=$(=max(YearMonthNum))"}>}value-to-calculate)

This will give YTD result

best regards

Chris

Re: Set Analysis Month & YTD

Fri, 24 Jan 2014 16:15:08 GMT

Thank You.

That was helpful but it doesn't allow me to chose a different month to the YTD.

Regards

Anna

Re: Set Analysis Month & YTD

Carlos_Reyes — Fri, 24 Jan 2014 19:24:57 GMT

And how will you decide which non-related YTD and Month you want to see?

The Month and YTD calculations are based on you Calendar selections... if you need to have an independent Month to see different periods you'll need to add an island Month or Calendar.

Re: Set Analysis Month & YTD

Mon, 27 Jan 2014 09:56:27 GMT

Hi Anna,

I took the expression from one of my application for which I can select a mont to change the reference

YearMonthNum is under this format YYYYMM (ex : 201310) and is store in an Event table but you could do the same while testing fields from MasterCalendar table

May be you can post your qvw

best regards

Chris

Re: Set Analysis Month & YTD

Mon, 27 Jan 2014 10:09:41 GMT

Hi,

Please check this YTD Calculation

Thank you

Vardhan

Re: Set Analysis Month & YTD

Mon, 27 Jan 2014 15:20:24 GMT

Are you saying I need to have a second calendar Carlos Alberto Reyes Díaz?

Re: Set Analysis Month & YTD

Carlos_Reyes — Mon, 27 Jan 2014 15:34:53 GMT

Well.. it depends on how you want to handle the independence of your calculations. I think you need to create a second calendar, or at least add an island month or something, because of your first post example.

"I would be able to make a straight table that allows me to see a particular month and a different YTD (ie.Month 12 and SepYTD) using flags."

Since YTD is based on the maximum Date selected or possible, if you want to see Sep YTD, that means that you're gonna select a date field that indicates that Septmember is the maximum month, so... I can't think of how will you tell QlikView to show December on the second calculation... is it gonna be random? Or... if you want to use flags to show December in the second calculation you have to do it in the script so that when September is the max Month for YTD, December is the Month to be shown in the second calculation... but what happens if you want to change the Month to be shown in that sencond expression? In that case flags become useless... as far as I can think...

So... I recommend to you to use an island calendar since this will make it easier for you to handle any combination.. although you'll need to use Set Analysis.