-
Re: Express KPI of Year (Today)
pradosh thakur Jan 5, 2018 2:25 AM (in response to Tom Müller)try removing the '=' after $
why is '=' there btw? and you can't do sum(sum()) and ' <' is missing . copy paste your expression with a little explanation so that we can help you better
-
Re: Express KPI of Year (Today)
Tom Müller Jan 5, 2018 2:26 AM (in response to pradosh thakur)Thanks for your reply, my forumla looks like this now:
(Sum({$Year={"$(=Year(Today)())"}>}(Sum(Kosten)+Sum(Materialeinkauf))))/((Sum({$Year={"$(=Year(Today)())"}>})(Sum([Erlös]))+Sum(Schlussrechnung)))
But the result is the same, there is no output.
I copied the formula from a youtube QLIK Course, as it worked I expected it to work with my data too.
-
Re: Express KPI of Year (Today)
pradosh thakur Jan 5, 2018 2:34 AM (in response to Tom Müller)not sure about the requirement but this is the best i can think off
=(Sum({<Year={"$(=Year(Today)())"}>}Kosten)+Sum({<Year={"$(=Year(Today)())"}>}Materialeinkauf))
/(Sum({<Year={"$(=Year(Today)())"}>}[Erlös])+Sum({<Year={"$(=Year(Today)())"}>}Schlussrechnung))
-
Re: Express KPI of Year (Today)
Tom Müller Jan 5, 2018 2:49 AM (in response to pradosh thakur)Now the result is equal to (((Sum(Kosten)+Sum(Materialeinkauf))/((Sum(([Erlös]))+Sum(Schlussrechnung))))), so it includes the set filter.
-
Re: Express KPI of Year (Today)
pradosh thakur Jan 5, 2018 2:53 AM (in response to Tom Müller)as i told you i am not aware of your requirement so you have to explain . and you are probably getting data for 2018. check if you have any different years.
-
Re: Express KPI of Year (Today)
Tom Müller Jan 5, 2018 3:20 AM (in response to pradosh thakur)My requirement is to get a KPI that displays the amount of costs, that external services caused in ratio to my revenue for the current year. So the value of the KPI shall stay the same when I change my filters.
Thanks for your help anyway and sorry for my bad explanations, as english is not my native language the technical terms aren't always in my mind.
-
Re: Express KPI of Year (Today)
pradosh thakur Jan 5, 2018 3:30 AM (in response to Tom Müller)Try this
=(Sum({1<Year={"$(=Year(Today)())"}>}Kosten)+Sum({1<Year={"$(=Year(Today)())"}>}Materialeinkauf))
/(Sum({1<Year={"$(=Year(Today)())"}>}[Erlös])+Sum({1<Year={"$(=Year(Today)())"}>}Schlussrechnung))
It's fine my friend. Knowledge in English is not the only parameter to measure someone capability. Cheers.
-
Re: Express KPI of Year (Today)
Tom Müller Jan 5, 2018 3:39 AM (in response to pradosh thakur)Thanks for the reply. I think we're getting closer. Now the value stays the same, but it shows the result of all given data, but not of the current year. Seems to be logic for me as "1" includes (as far as my knowledge goes) all given data. But I wonder, why "$" doesn't work, maybe do I have to change something else in my data editor?
-
Re: Express KPI of Year (Today)
pradosh thakur Jan 5, 2018 3:54 AM (in response to Tom Müller)ahh .... got it.. my mistake . didn't see today() had one extra bracket
=(Sum({1<Year={"$(=Year(Today()))"}>}Kosten)+Sum({1<Year={"$(=Year(Today()))"}>}Materialeinkauf))
/(Sum({1<Year={"$(=Year(Today()))"}>}[Erlös])+Sum({1<Year={"$(=Year(Today()))"}>}Schlussrechnung))
-
Re: Express KPI of Year (Today)
Tom Müller Jan 5, 2018 4:57 AM (in response to pradosh thakur)You're right, there was a mistake. But it's not the solution, furthermore the formula equals to (((Sum(Kosten)+Sum(Materialeinkauf))/((Sum(([Erlös]))+Sum(Schlussrechnung))))).
What I could find out is, that the perimeter Year(Today()) is ignored, as I can change today to any other word, but nothing happens.
-
-
Re: Express KPI of Year (Today)
Sunny Talwar Jan 5, 2018 7:10 AM (in response to pradosh thakur)Will check in a bit
-
-
-
-
-
-
-
-
-
-
-
-
Re: Express KPI of Year (Today)
Sunny Talwar Jan 5, 2018 9:08 AM (in response to Tom Müller)Don't see any obvious mistakes in this expression
=(Sum({1<Year = {"$(=Year(Today()))"}>} Kosten) + Sum({1<Year = {"$(=Year(Today()))"}>} Materialeinkauf))
/(Sum({1<Year = {"$(=Year(Today()))"}>} [Erlös]) + Sum({1<Year = {"$(=Year(Today()))"}>} Schlussrechnung))
But I am going to ask few questions which may sound stupid, but are the problem a lot of the times
1) Do you have a field called Year? If not, are you replacing Year above with your Year field?
2) Qlik Sense has a auto generated calendar, is your field auto generated year field? If it is, the set analysis might not work on those fields
Derived fields not working in Set Analysis. To fix this, create a new field in the script
3) Is Year field linked to everything else in your database? If isn't, then set analysis can't really do anything