Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
Showing results for 
Search instead for 
Did you mean: 

YTD Totals

I have the YTD Total based on the selections usign this formula:

Sum({<Asset_FiscalMonth={"<=$(=vMaxFiscalMonth)"}>} [Total Revenue])

I'm putting it in a Pivot Table and using FY as a dimension, so it seems to work OK.

What I need is the Total Revenues for the Year, YTD, so I can get a % of YTD Revenues based on the current selections.

I thought the formula would just be:

Sum({1<Asset_FiscalMonth={"<=$(=vMaxFiscalMonth)"}>} [Total Revenue])

But, that didn't seem to work. (I think that gives me the total for ALL years.)

What would I need to do to have the formula recognize the FY dimesion from the Pivot Table?


3 Replies

Sum(Total {<Asset_FiscalMonth={"<=$(=vMaxFiscalMonth)"}>} [Total Revenue])


Sum(All {<Asset_FiscalMonth={"<=$(=vMaxFiscalMonth)"}>} [Total Revenue])

I should add that both of the above return the same results. I would think that ALL would give me what I'm seeing on my formula above, but TOTAL would give me the result I was looking for, given that the field Asset_FiscalYear is a dimension in the Pivot Table.

Do you need % of YTD?

Num(Sum({<Asset_FiscalMonth={"<=$(=vMaxFiscalMonth)"}>} [Total Revenue])/

Sum(TOTAL {<Asset_FiscalMonth={"<=$(=vMaxFiscalMonth)"}>} [Total Revenue]),'##,#0%')

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Sorry for the late reply - I was away on vacation and am just getting back to this...

This portion of your formula seems to give me the correct value (YTD [Total Revenue]):

Sum({<Asset_FiscalMonth={"<=$(=vMaxFiscalMonth)"}>} [Total Revenue])

The problem with this part of the formula:

Sum(TOTAL {<Asset_FiscalMonth={"<=$(=vMaxFiscalMonth)"}>} [Total Revenue])

When used in a Pivot, it doesn't respect the dimension, so when I put the FY as a Column, the formula doesn't give me the fiscal year Total, it gives me the Total for ALL fiscal years. I need the formula to respect the FY column in the table as well as having it respect any additional selections.

I hope that makes sense...Thanks for the reply!