topic Re: Master Calendar - YTD in QlikView

Master Calendar - YTD

Fri, 11 Jul 2014 11:13:35 GMT

Hi there,

I've added a master calendar in my dashboard

I then created a Period variable in my main dataset which links to the calendar fine.

I've create the YTD variable (vSetYTD) below:

PeriodID = {"<=$(=Max(PeriodID))"},

Year = {$(=Max(Year))},

Quarter = ,

Period = ,

Month =

I have listboxes Year, month and Quarter on the dashboard. I'm trying to create a straight table to sum the YTD value depending on what time period the user selects. in the table I want time in the dimensions. See below:

Dimension

Year Month

2014 Jan

2014 Feb

2014 Mar

2014 Apr

2014 May

Expression:

Sum(value) for YTD. I've been using the expression sum({$<$(vSetYTD)>} [value]) but not giving me what I want.

So the Feb figure will be Jan+Feb, March will be Jan+Feb+Mar and so on.

I'm hoping it's something obvious I'm not doing. I assumed once I created the YTD variable I could just drop it into an expression and it would work!

Hope someone out there can help!

Re: Master Calendar - YTD

ali_hijazi — Fri, 11 Jul 2014 11:22:17 GMT

the YTD would be something like this

sum({<Month=,Year={$(=max(Year))},period={"<=$(=max(period))"}>} value)

you need to ignore selection on month to be able to go back to start of year

Re: Master Calendar - YTD

veidlburkhard — Fri, 11 Jul 2014 11:22:22 GMT

Hi Adrian,

only to make sure everything is ok, can you check in the expression tab the full accumulation chec box is not marked!?!

Burkhard

Re: Master Calendar - YTD

Fri, 11 Jul 2014 11:43:27 GMT

Hi Ali,

I've changed the sum to what you have suggested but no difference.

However,Burkhard, full accumulation is not ticked, but when I select this option, it's kind of doing what I want!

It's adding the previous month to the next month which is what I want. Only thing is just works when you have Year selected though.

When I select a year and a month, the table shows the Year and all the months in that year.

Adrian

Re: Master Calendar - YTD

ali_hijazi — Fri, 11 Jul 2014 11:45:28 GMT

ignore selection on period too if you are selecting the period on the sheet

Re: Master Calendar - YTD

Fri, 11 Jul 2014 11:59:36 GMT

On the sheet I have Year, Month and Quarter. On the table I have Year-Month as a dimension. I don't think this accumulation will work as if user selects multiple years,quarters or months, it falls over.

When I select 2013 and 2014 the table only shows 2014 year and month.

Adrian