Sum with multiple conditions and formula inside

[Frinolav]

Hi everyone,

I am a beginner in QlikSense and I am trying to put an expression for customed color. I get an error with the following formula :

if(sum({<[TNO.Année]={'2021'}, [TNO.mois]={month(today())}>}[Temps utile])/sum({<[TNO.Année]={'2021'}>}[Temps Net d'ouverture])*100 < 40, '#ce242e','#3ba63b')

I guess the error is in this part : sum({<[TNO.Année]={'2021'}, [TNO.mois]={month(today())}>}

Can someone please help ?

Thanks !

[ChristofZ]

thank you - now the Qlik-error is fixed, but the condition doesn't work:

if I choose

TheDate = May 15th '24 

--> WeekStart(filterDate-21) - WeekEnd(filterDate-21) = April 22th - 28th 

which contradicts the second condition that

MonthStart(filterDate) .. MonthEnd(filterDate) = May

so my numbers should all be zeroes, instead they are ~10x higher than in normal weeks.

I had the same issue with contradicting conditions (see above) - clearly a bug in Qlik.

[lennart_mo]

Had to try out the expression on one of my own datasets, apparently the space in between '<=' and the $ sign expression was the problem, so now you should get your desired result with the formula below.

Sum(
{1< 
TheDate = {">=$(= MonthStart(filterDate)) <=$(= MonthEnd(filterDate) )"}
*
{">=$(= WeekStart(filterDate-21)) <=$(= WeekEnd(filterDate-21) )"}
>}
Money
)

[ChristofZ]

YES! That's it 😎

Thank you very much!! My numbers are correct.

Need to remember that one ...

So one condition in a SUM accumulation looks like this:

=
Sum(
{1< 
TheDate = 
{">=$(= MonthStart(FilterDate)) <=$(= MonthEnd(FilterDate) )"}
>}
Money
)

two conditions:

=
Sum(
{1< 
TheDate = 
{">=$(= MonthStart(FilterDate)) <=$(= MonthEnd(FilterDate) )"}
*
{">=$(= WeekStart(FilterDate-7)) <=$(= WeekEnd(FilterDate-7) )"}
>}
Money
)