18 Replies Latest reply: Dec 27, 2017 11:26 AM by Sunny Talwar

# set analysis MONTH-1

whe i used this expressioin

Sum( {<COMPTE_ENCOURS_TND={">0"} ,DATE_SITUATION= {"=MONTH(DATE_SITUATION) = '11'"}, COMPTE_TYPE_DEPOT={"DAV"},CLIENT_PRE_SOUS_SEGMENT_A,CLIENT_PRE_SEGMENT_A,CLIENT_PRE_SEGMENT_A={"HAUT DE GAMME"}>} COMPTE_ENCOURS_TND)

i have the correct resultat , and when i want make this expression dynamic
Sum( {<COMPTE_ENCOURS_TND={">0"} ,DATE_SITUATION= {"=MONTH(DATE_SITUATION) = \$(=MAX(MONTH(DATE_SITUATION))-1) "}, COMPTE_TYPE_DEPOT={"DAV"},CLIENT_PRE_SOUS_SEGMENT_A,CLIENT_PRE_SEGMENT_A,CLIENT_PRE_SEGMENT_A={"HAUT DE GAMME"}>} COMPTE_ENCOURS_TND)

i have 0 as result

• ###### Re: set analysis MONTH-1

Did you not try the Max(TOTAL...) suggestion yet souad? I would really encourage you to try this

Sum({<COMPTE_ENCOURS_TND={">0"}, DATE_SITUATION= {"=MONTH(DATE_SITUATION) = (MAX(TOTAL MONTH(DATE_SITUATION))-1)"}, COMPTE_TYPE_DEPOT={"DAV"}, CLIENT_PRE_SOUS_SEGMENT_A, CLIENT_PRE_SEGMENT_A = {"HAUT DE GAMME"}>} COMPTE_ENCOURS_TND)

• ###### Re: set analysis MONTH-1

even with total i have 0:(

• ###### Re: set analysis MONTH-1

Can you check what do you get when you use this in a text box or KPI object

=MAX(TOTAL MONTH(DATE_SITUATION))-1

• ###### Re: set analysis MONTH-1

i have 10 as resultat is not true the max date i have 08/12/2017

• ###### Re: set analysis MONTH-1

Have you made any selections which might be causing this? Do you wish to ignore selections? May be try this

=MAX({1}TOTAL MONTH(DATE_SITUATION))-1

• ###### Re: set analysis MONTH-1

i have 10

• ###### Re: set analysis MONTH-1

Screenshot of DATE_SITUATION in a list box or filter pane object?

• ###### Re: set analysis MONTH-1

AND WHEN I USED YEAR THE RESULT IS 57 125

• ###### Re: set analysis MONTH-1

Year is 57125? Are you sure your date is read as date by QlikView/Qlik Sense? It almost seems like Dates are not understood as date for some reason

• ###### Re: set analysis MONTH-1

ewemple 20171203

script

DATE(DATE_SITUATION,'YYYYMMDD') as DATE_SITUATION

• ###### Re: set analysis MONTH-1

This is helpful, but can you also post a screenshot of DATE_SITUATION in a list box object

• ###### Re: set analysis MONTH-1

Can you add this expression in a text box object and post another screenshot

=Max(DATE_SITUATION) & Chr(10) & Max(Month(DATE_SITUATION)) & Chr(10) & Max(Year(DATE_SITUATION))

• ###### Re: set analysis MONTH-1

SUNNY I fixed the problem format date , and i have month -1 =11 and year =2017 , and i used

Sum({<COMPTE_ENCOURS_TND={">0"}, DATE_SITUATION= {"=MONTH(DATE_SITUATION) = (MAX(TOTAL MONTH(DATE_SITUATION))-1)"}, COMPTE_TYPE_DEPOT={"DAV"}, CLIENT_PRE_SOUS_SEGMENT_A, CLIENT_PRE_SEGMENT_A = {"HAUT DE GAMME"}>} COMPTE_ENCOURS_TND)

the resultat is not true ,

• ###### Re: set analysis MONTH-1

So, you would have expected to see only 08/12/2017 for MONTH and 30/11/2017 for MONTH-1? Is that right, can you remove the labels and show both the expressions?

• ###### Re: set analysis MONTH-1

for 08/12/2017 in MONTH I-1    138 296 472

FOR 30/11/2017 IN MONTH -1  0

• ###### Re: set analysis MONTH-1

May be this

Above(TOTAL Sum({<COMPTE_ENCOURS_TND={">0"}, DATE_SITUATION= {"=MONTH(DATE_SITUATION) = (MAX(TOTAL MONTH(DATE_SITUATION))-1)"}, COMPTE_TYPE_DEPOT={"DAV"}, CLIENT_PRE_SOUS_SEGMENT_A, CLIENT_PRE_SEGMENT_A = {"HAUT DE GAMME"}>} COMPTE_ENCOURS_TND))

• ###### Re: set analysis MONTH-1

but sunny when i selected situation date return 0

• ###### Re: set analysis MONTH-1

How about if you delete the set analysis for DATE_SITUATION here and Multiply by Avg(1) like this

Above(TOTAL Sum({<COMPTE_ENCOURS_TND={">0"}, DATE_SITUATION, COMPTE_TYPE_DEPOT={"DAV"}, CLIENT_PRE_SOUS_SEGMENT_A, CLIENT_PRE_SEGMENT_A = {"HAUT DE GAMME"}>} COMPTE_ENCOURS_TND)) * Avg({<COMPTE_ENCOURS_TND={">0"}, COMPTE_TYPE_DEPOT={"DAV"}, CLIENT_PRE_SOUS_SEGMENT_A, CLIENT_PRE_SEGMENT_A = {"HAUT DE GAMME"}>}1)