Flag based on revenue

[Anonymous]

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

[devarasu07 · Accepted solution]

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

[Anonymous]

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]

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

[Anonymous]

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