Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
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')
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')
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')
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 !
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')
Thank you very much, it works ! 🙂
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
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 ...
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!
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 ...
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!