Dimension with sum condition

[ZimaBlue]

Hello!

I am trying to create a dimension that will contain 1 or 0, depending on the condition of the cost sum in orders. When I add conditions to the pivot table as separate measures, the sums are calculated correctly and I see that there are many rows for the condition '1'. However, the dimension shows me '0' in 100% of cases.

I already have another dimension also based on sum conditions and it works fine. Why can't qlik sense correctly detect this?

dimension

=if( aggr(sum({<order_date={">=$(vPrevFrom)<=$(vPrevTo)"}>} order_cost), user_id) =0 and aggr(sum({<order_date={"<$(vPrevFrom)"}>} order_cost), user_id) >0, '1', '0')

[rubenmarin]

Hi, in that table you can add the expressions to cehck the calcualted, just add 2 measures:

- aggr(sum({<order_date={">=$(vPrevFrom)<=$(vPrevTo)"}>} order_cost), user_id)

- aggr(sum({<order_date={"<$(vPrevFrom)"}>} order_cost), user_id)

And confirm it return the values you expect. Probably both return null because an aggr usually returns many diffeernt values and you need to enclose them in an aggregation function that tells what to do with all that different values, in example:

sum(aggr(sum({<order_date={">=$(vPrevFrom)<=$(vPrevTo)"}>} order_cost), user_id))

Also note that set analysis is calulated before the table rows, so dimensions values will not have an impact, all rows/dimesnions will apply the same set analysis

[ZimaBlue]

Hello! I tried inserting your expressions a measure and it doesn't display the correct values, it works as a measure only without aggr 😞 I use aggr only for dimension, because as far as I understand, including the formula in the dimensions does not work without aggr.

At the same time, I have an absolutely identical dimension that displays the correct values. Another dimension is if( aggr(sum({<order_date={">=$(v30d)<=$(vToday)"}>} order_cost), user_id) >= 50000, '1. Qextra (50k+)', 'Low'). The logic of the formula is the same. I tried to copy this dimension, but replace 'qextra' and 'low' with 1 and 0 - it works fine.

I also tried using the option aggr(if( sum({<order_date={">=$(v30d)<=$(vToday)"}>} order_cost) <1, '1', '0'), user_id) and in this case I get null where there should be 1 and 0 for all 0. 

There are some problems with comparing numbers with 0 in dimensions with formula. I try to use the condition =0 or <1 and it cannot work with this correctly for some reason

[ZimaBlue]

I replaced dimension with =if( aggr(sum({<order_date={">=$(vPrevFrom)<=$(vPrevTo)"}>} order_cost), user_id) >=100, '0', '1') and now it works. It's not that 100% suits me, but comparing with 0 and 1 doesn't work at all.

[SunilChauhan]

try this

if(Sum( aggr(sum({<order_date={">=$(vPrevFrom)<=$(vPrevTo)"}>} order_cost), user_id) )=0 and Sum(aggr(sum({<order_date={"<$(vPrevFrom)"}>} order_cost), user_id)) >0, '1', '0')

[rubenmarin]

Hi, you're right, I miss the part when you say it was used as dimension.

In that case, just to make the test, you can create a table with user_id as dimension, and the expression "sum({<order_date={">=$(vPrevFrom)<=$(vPrevTo)"}>} order_cost)" and another with "if( sum({<order_date={">=$(vPrevFrom)<=$(vPrevTo)"}>} order_cost) =0,1,0)". So you can check if any user_id returns 0, and if it's correctly identified by the 'if'. 

And the same with the other expressions you want to test. Hoping to get some hints based on this results.