Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mslottje
Contributor II

Flag based on revenue

Hi experts,

for our sales team, I've plotted our sales locations on a GeoMap in Qlik Sense.

The size of the dots are based on the revenue in the current year and these dots turn green if the revenue > 0.

If the revenue < 0, the dot turns red.

To help our sales team, I want to make a flag which is based on the revenue of the current year.

I tried to make a filter in a worksheet with the following set analysis expression:

if(SUM({<Year = {$(=Year(Today()))}>}[Nett Amount new EUR])>0,'Green','Red')

Unfortunately, Qlik shows me an invalid dimension as outcome in the filter pane.


Should I change the expression or should I create a flag in the load script?

If I should create this flag in the load script, how should the expression look like?


Your help is appreciated!

Mike



1 Solution

Accepted Solutions
devarasu07
Honored Contributor II

Re: Flag based on revenue

Hi,

the expression looks fine. do u wanted to apply this expression in filter pane? if so then u have use like below kind expression

=Aggr(if(SUM({<Year = {$(=Year(Today()))}>}[Nett Amount new EUR])>0,'Green','Red'),[Nett Amount new EUR] )


P.S: aggr(expression, dimensions)


Thanks,

Deva


4 Replies
devarasu07
Honored Contributor II

Re: Flag based on revenue

Hi,

the expression looks fine. do u wanted to apply this expression in filter pane? if so then u have use like below kind expression

=Aggr(if(SUM({<Year = {$(=Year(Today()))}>}[Nett Amount new EUR])>0,'Green','Red'),[Nett Amount new EUR] )


P.S: aggr(expression, dimensions)


Thanks,

Deva


mslottje
Contributor II

Re: Flag based on revenue

Yes, I want to apply this expression in a filter pane, so our sales team can filter on red and green dots.

The Aggr function did solve my problem, so many thanks for that!

Could you shortly explain what the Aggr function exactly does in this example?

And does this expression also work within the load script, so I can create this flag as a dimension?

Mike

devarasu07
Honored Contributor II

Re: Flag based on revenue

Hi,

Check this article, yes we can do backend script aswell

Aggr function in QlikView – Learn QlikView

There are many ways you can do it

1) Color code using inline load script

2) if your re-using this prompt in your application better create master dimension and easy to call all the sheets.

Thanks,

Deva

mslottje
Contributor II

Re: Flag based on revenue

That helps to understand the function, thanks!

Now I'm trying to create the same flag in the back end, but I'm getting stuck.

Could you show an example of using a group by clausule with the same field names?

I prefer to have this as a dimension in my load script, because I'll have to use in multiple apps..