YTD in a Pivot table with dimension both on horizontal and vertical axis.

[Christoffer93]

Hello Qlik community

Im currently working with a Qlikview app and i am having some troubles with my YTD values.

The problem is that i can't get my YTD values to show per month when the month dimension is on the horizontal axis. It just show the "regular" values for each month and does not accumulate them.

Let's say for example that i want to be able to select a month and the outcome should show the YTD value up to the selected month. When noting is selected it should show all the months and their respective YTD values. 

Im currently using this formula to calculate YTD. It is working when i don't have i dimension on the horizontal axis.

SUM({$<Year={$(=Max(Year))},Month= {"<=$(=max({<Year={$(=Only(Year))}>} Month))"}>}value)

[blahdiqlik]

On the expressions tab of your chart's properties, select the expression and then tick "full accumulation" for the accumulation setting.

[Christoffer93]

It's not possible to do that in a pivot table. It's greyed out.

[blahdiqlik]

Apologies, didn't see you mentioned it being a pivot table.

If you you are going to work a lot with YTD values, then it will be best create a YTD-field in your data's load script. You will have to use the rangesum function. Firstly, your data must be sorted correctly (use  "Order by" and fields that must be sorted in priority in a resident load), then you can use a rangesum formula to define a YTD-field.

An example...

If you have four fields in your data table (let's say the data table is tempDATA_1): Product_field, Year_field, Month_field, Value_field.

Resident load your data

NoConcatenate

tempDATA_2:

LOAD *

Resident tempDATA_1

Order by Product_field, Year_field, Month_field;

DROP Table tempDATA_1;

(you don't have to specify ascending (asc), only descending (desc) if your data must be sorted that way, but not in this instance)

Then in a next load...

DATA:

LOAD *,

   if(Product_field & Year_field = Previous(Product_field & Year_field),

      Rangesum(Value_field, Peek('Value_YTD_field')), Value_field) as Value_YTD_field

Resident tempDATA_2;

Drop table tempDATA_2;

Now you have a field you can reference in your pivot table for YTD, just replace the mention of your value_field with the newly created Value_YTD_field in your existing expression.

Please note, if you're data is transactional, i.e. multiple transactions for the same products against multiple dates within a month, then you will have to employ "Group by" as well and not load those deeper granular fields for the YTD load script mentioned above to work.