Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
liviumac
Creator
Creator

Calculated dimensions as set modifiers

Hello

I hope I'll be able to explain what I want to achieve, so here it goes:

1. database fields: reporting_date, client, numerical_attribute, sales

2. calculated dimension: for any 2 selected dates, if the numerical attribute of the client has increased, I define a new category named, let's say, "UP", if it has decreased then its "DOWN", stayed the same is "NO CHG"

since I want to be able to calculated this dimension for any 2 reporting dates upon making a selection, I use in the dimension as set modifiers 2 variables based on the reporting date selected (one variable calculates the largest date and the other the smaller date)

so basically my dimension calculates, for any 2 selected reporting dates and for each client, whether the numerical attribute has increased or decreased and assigns to the client either an UP or a DOWN or a NO CHG

I cannot implement this in the script because whether the client is included in UP/DOWN/NO CHG depends on what 2 dates are compared, i.e. the same client can be in eg UP when 2 dates are selected and it can be in eg DOWN when another 2 dates are selected

3. what I want to do is calculate total sales (or increase in sales, it doesn't matter) for the clients which, for 2 dates, are in the UP category and only in this category (or in the DOWN etc, but only for that category)

if the dimension I calculate was a field, I would use a normal modifier sum({<dimension={'UP'}>}sales)

but if instead of "dimension" I write the formula for the dimension, it does not work

if I put the calculated dimension in a table, it works, but I need the value only for each category at a time (for a waterfall chart)

12 Replies
OmarBenSalem
Partner
Partner

I would put all of this :

Month={"=sum(Sales)>=600 and sum(Sales)<=800"}


please write as follow without the '='

=Month={"=sum(Sales)>=600 and sum(Sales)<=800"}


in a variable; let's called vSalesBtw600And800


so the expression would become :

sum({<$(vSalesBtw600And800)>}Sales)

OmarBenSalem
Partner
Partner

ps: when u call a variable

u call it like this:

$(vSalesBtw600And800)


and not $(=vSalesBtw600And800)

because variable already implicitly contains the =

it's x= the variable

so if u reuse the =; it's like u've written $(==ur variable expression)

Hope that was clear..

liviumac
Creator
Creator
Author

thank you, yes, the extra equal sign was the problem

great stuff