Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for 
Search instead for 
Did you mean: 
Frinolav
Contributor II
Contributor II

Sum with multiple conditions and formula inside

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 !

2 Solutions

Accepted Solutions
sunny_talwar

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

View solution in original post

Vegar
MVP
MVP

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

View solution in original post

12 Replies
sunny_talwar

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
Contributor II
Contributor II
Author

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 ? 🙂

testqliksense.jpg

 

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

 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
Contributor II
Contributor II
Author

Thank you very much, it works ! 🙂

ChristofZ
Contributor II
Contributor II

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
Contributor II
Contributor II

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

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
Contributor II
Contributor II

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

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!