How to filter the data for the previous months with Set Analysis

[emillanc]

Hi all,
I'm trying to generate some insights from a table with the following structure:

Table with Data

When I try to show in a KPI card the Sum of the Billed amounts with the data filtered for the previous months using this formula:
Sum({$<FYM_Avg = {"<=$(=Year(AddMonths(Today(), 0)) & Num(Month(AddMonths(Today(), 0)), '00'))"}>} Billed_Total_Avg)

I always get 0 in the 'Billed' KPI card:

Please your help to find the error in the formula

Regards

Emmanuel

[Oleg_Troyansky · Accepted solution]

Hi Emmanuel,

Your formula looks OK (I don't quite understand the role of AddMonths() with 0, but it shouldnt't cause any issues). I'd recommend examining the data format of your field FYM_Avg - is it a 6-digit number? Or a string? Or a date field, formatted as 'YYYYMM' ?

For example, what would happen if you enclosed your $-sign expansion in single quotes? Would that formula produce the expected number?

Sum({$<FYM_Avg = {"<='$(=Year(AddMonths(Today(), 0)) & Num(Month(AddMonths(Today(), 0)), '00'))'"}>} Billed_Total_Avg)

To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

[emillanc]

Thank for your answer!

The idea of using  AddMonths is generate data with the same format that FYM_Avg  field (202306, 202307, 202308), using the current date.

I solved the issue with a Cast(FYM_Avg  As int64 ) in the query and using the same formula than above.