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 !

[sunny_talwar · Accepted solution]

Try this

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')

[Frinolav]

Thanks a lot, this helps ! Unfortunately I still have an issue... when i put this formula it only applies to the last value and not to all the graph. I would like that all the graph is colored depending on the last value compared to a target. Can you help please ? 🙂

For example, i would like all the graph to be red if the latest value is below 40 and green if it is above 40.

Thanks !

[Vegar · Accepted solution]

 A TOTAL in your nominator might do the trick. Try this:

If(Sum({<[TNO.Année] = {'2021'}, [TNO.mois] = {"$(=Month(Today()))"}>} TOTAL [Temps utile])/Sum({<[TNO.Année] = {'2021'},[TNO.mois] = {"$(=Month(Today()))"}>} TOTAL [Temps Net d'ouverture]) * 100 < 40, '#ce242e','#3ba63b')

[Frinolav]

Thank you very much, it works ! 🙂

[ChristofZ]

good morning,

I have a similar very simple problem:

I have one date, but I want to filter it twice in a SUM in a table formula:

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

The idea is I want to filter payment for the days of the week inside the same month, and if the week is entirely outside that month, I want all zeroes in my column, but that doesn't work. I also tried putting the 2 conditions in the same line, like:

TheDate = {"$(= 
'>=' & WeekStart(filterDate-21) & '<=' & WeekEnd(filterDate-21) & 
'>=' & MonthStart(filterDate) & '<=' & MonthEnd(filterDate) )"}

but neither works ...
Thanks for your help!

Regards, Christof

[ChristofZ]

funny - if I do this:

Sum(
TheDate = {"$(= '>=01.06.2024<=30.05.2024' )"}
Money
)

 it sums up everything - the contradictory condition seems to encourage Qlik to sum up all the money instead of giving zeroes ...

[lennart_mo]

Hi @ChristofZ,

try this:

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

 Maybe you'll need to add a Month Field in your Data model to achieve the second condition.

Let me know, if this works for you!

[ChristofZ]

Hi - thank you @lennart_mo!

No - Qlik issues an "Error in set modifier expression" ... 🤔

Meanwhile, I helped myself by starting my first week like

=
Sum(
{1< 
TheDate = {"$(= '>=' & MonthStart(filterDate) & '<=' & WeekEnd(MonthStart(filterDate)) )"}
>} 
Money
)

That's the only workaround I got working ...

[lennart_mo]

Hi,

it seems i had a slight syntax error in my expression.

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

Retry it with this one!