Returns sum 0, if selected month does have data

[simonQlik6]

In Qlikview(version: 12.20 November 2017) I have a formula that calculates the sum of values for the past year, when selecting from the filters a year and a month, according to the selection the sum is calculated for the last 12 months. (Example: if December 2021 is selected, then sum is calculated from January to December 2021).

The formula doesn’t work however, when the selected month doesn’t have any data, the sum returns as 0, even if there are data for other months. 

[Mark_Little]

Hi,

Can you please share your set analysis and maybe we can spot a mistake or help a little more.

[simonQlik6]

Hi, the set analysis below is used: 

(((((Sum ({1<Class=P(SourceName),Type=P(BsTrans.BusinessClass),ClmCoverCode=P(CoverCode), ClmChannel = P(Channel),ClaimDateID={">= $(=Only({$<ClmMny.TranDateYear=P(Year), MonthTranDate=P(Month)>} ClaimDateID)-11) <= $(=Only({$<ClmMny.TranDateYear=P(Year), MonthTranDate=P(Month)>} ClaimDateID))"}>} ClaimsPaid))))))

[Eloiseparkinson]

The issue could be that when you select a month with no data, QlikView doesn't consider the other months in the year since they're not part of the selected set of data. Try modifying your formula to include the set analysis feature, which allows you to define a set (a group of data values) that's different from the currently selected set. This way, you can include all the months of the selected year in the calculation, regardless of whether the selected month has data or not. If you continue to face issues, consider reaching out to Qlik's support forum or the QlikView community for specific assistance related to your formula. Be ready to share details about your data and the exact formula you're using.