Year To Date table - but filterable

[alenb]

With the help community I have previously come up with very nice solution to calculate Year To Date sums. Check here:

YTD:
Load BillingYear as ytd_Year, sum(Units) as ytd_Units Resident SALES_PER_MONTH Where BillingMonth <= $(PreviousMonth) Group By BillingYear;

But since this is now grouped by year, I can't filter it on say CustomerID, to see specific YTD figure for specific customer or product or other dimensions.

I can include it like this:

YTD_PER_CUSTOMER_ID:
Load CustomerID as ytd_CustomerID, BillingYear as ytd_Year, sum(Units) as ytd_Units Resident SALES_PER_MONTH Where BillingMonth <= $(PreviousMonth) Group By BillingYear, CustomerID;

YTD_PER_PRODUCT_ID:
Load ProductID as ytd_ProductID, BillingYear as ytd_Year, sum(Units) as ytd_Units Resident SALES_PER_MONTH Where BillingMonth <= $(PreviousMonth) Group By BillingYear, ProductID;

But then I'd have to do a new table for every dimension. It's doable, but doesn't seem very elegant. Is there a better way?

[chrismarlow]

Hi,

You could use chart functions - so Above + Range functions, see;

Above - chart function ‒ Qlik Sense on Windows

That should avoid hard coding values in your script.

Cheers,

Chris.

[alenb]

I'd appreciate if you could provide a very simple example for what I need, since it wasn't that clear to me from the documentation. 

[chrismarlow]

Hi,

I've attached a sample file with columns names similar to what you seem to be using.

I got a bit distracted as there are some really good threads that go into how to handle some issues with this, so well worth you looking at;

Solved: YTD calculation that works with filtered date dime... - Qlik Community - 55411

On what I have attached the first RangeSum is the 'simple' version (works if you have no missing months etc.), the second one uses some of the tips in the thread, so will work if you had no data for a customer/product for a month.

Cheers,

Chris.