topic Sum with multiple conditions and formula inside in QlikView

Sum with multiple conditions and formula inside

Frinolav — Fri, 10 Sep 2021 18:26:09 GMT

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 !

Re: Sum with multiple conditions and formula inside

sunny_talwar — Fri, 10 Sep 2021 19:14:16 GMT

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

Re: Sum with multiple conditions and formula inside

Frinolav — Fri, 10 Sep 2021 21:10:16 GMT

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 !

Re: Sum with multiple conditions and formula inside

Vegar — Fri, 10 Sep 2021 21:40:30 GMT

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

Re: Sum with multiple conditions and formula inside

Frinolav — Fri, 10 Sep 2021 22:15:32 GMT

Thank you very much, it works ! 🙂

Re: Sum with multiple conditions and formula inside

ChristofZ — Thu, 01 Aug 2024 07:55:43 GMT

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

Re: Sum with multiple conditions and formula inside

ChristofZ — Thu, 01 Aug 2024 09:50:55 GMT

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 ...

Re: Sum with multiple conditions and formula inside

lennart_mo — Thu, 01 Aug 2024 12:02:07 GMT

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!

Re: Sum with multiple conditions and formula inside

ChristofZ — Fri, 02 Aug 2024 05:56:10 GMT

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 ...

Re: Sum with multiple conditions and formula inside

lennart_mo — Fri, 02 Aug 2024 06:42:40 GMT

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!

Re: Sum with multiple conditions and formula inside

ChristofZ — Fri, 02 Aug 2024 07:33:16 GMT

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.

Re: Sum with multiple conditions and formula inside

lennart_mo — Fri, 02 Aug 2024 12:32:04 GMT

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
)

Re: Sum with multiple conditions and formula inside

ChristofZ — Mon, 05 Aug 2024 06:41:11 GMT

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
)