Get Quantity in Set Analysis 3 Month Ago with Sum Included

[pgalvezt]

Hello,

This Works for actual Month and  if I selected 3 Months (QlikSense Count total people with 90 Days too) But filter is just by 1 Month.

Num(Count({<Vs_II = {'P1'}, Vl_II = {">0"},
DF_II = {"=(Sum({<Vs_II = {'P1'}, Vl_II = {[>0]}>} Vl_II )/Divisor) =30"}>} Distinct DF_II ) / Divisor,'#.##0') (Works Fine)

I Want to count people  that they have in total 90 Days  (Doesn't Work)

Num(Count({<Vs_II = {'P1'}, Vl_II = {">0"},
DF_II = {"=(Sum({<Vs_II = {'P1'}, Vl_II = {[>0]},period_II = {'$(='>=' & Date(AddMonths(Max(period_II), -2), 'MMYYYY') & '<=' & Date(Max(period_II), 'MMYYYY'))'}, Month_II>} Vl_II )/Divisor) =90"}>} Distinct DF_II ) / Divisor,'#.##0')

Thank You !

[Kushal_Chawda]

try to include period of set in outer modifier as well

Num(Count({<Vs_II = {'P1'}, Vl_II = {">0"},period_II = {'$(='>=' & Date(AddMonths(Max(period_II), -2), 'MMYYYY') & '<=' & Date(Max(period_II), 'MMYYYY'))'}, Month_II>},DF_II = {"=(Sum({<Vs_II = {'P1'}, Vl_II = {[>0]},period_II = {'$(='>=' & Date(AddMonths(Max(period_II), -2), 'MMYYYY') & '<=' & Date(Max(period_II), 'MMYYYY'))'}, Month_II>} Vl_II )/Divisor) =90"}>} Distinct DF_II ) / Divisor,'#.##0')

[pgalvezt]

Hi,

The formula returned null value.

[Kushal_Chawda]

try this

Num(Count({<Vs_II = {'P1'}, Vl_II = {">0"},period_II = {'$(='>=' & Date(AddMonths(Max(period_II), -2), 'MMYYYY') & '<=' & Date(Max(period_II), 'MMYYYY'))'}, Month_II,DF_II = {"=(Sum({<Vs_II = {'P1'}, Vl_II = {[>0]},period_II = {'$(='>=' & Date(AddMonths(Max(period_II), -2), 'MMYYYY') & '<=' & Date(Max(period_II), 'MMYYYY'))'}, Month_II>} Vl_II )/Divisor) =90"}>} Distinct DF_II ) / Divisor,'#.##0')

[pgalvezt]

Hi,

Still 0,

I think the problem is when change the month value. look

that formula works when I have selected June 2020. This works because it is counting the people who have 180 days (6 Months ago). but when I select another month the value goes to 0. I would like to select May count people who have 180 days, that is, it should take the months of Dec 2019, Jan, Feb, Mar, Apr, May 2020. all this based in the formula that I put above. because that is working when I have selected June 2020.

Count({<DF_II = {"=(Sum({<Vs_II={'P1'}, Vl_II = {"">0""},period_II = {""$(='>=' & Date(AddMonths(Max(period_II), -6), 'MMYYYY') & '<=' & Date(Max(period_II), 'MMYYYY'))""},Month_II>}Vl_II)) = 180"}>} Distinct DF_II ).

[pgalvezt · Accepted solution]

Hi,

It only remained to add the Year field

Count({<DF_II = {"=(Sum({<Vs_II={'P1'}, Vl_II = {"">0""},period_II = {""$(='>=' & Date(AddMonths(Max(period_II), -6), 'MMYYYY') & '<=' & Date(Max(period_II), 'MMYYYY'))""},Year_II,Month_II>}Vl_II)) = 180"}>} Distinct DF_II ).

Thanks!!