Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
william_fu
Creator II
Creator II

Measure evaluating by the max dimension

This is hard to explain, but I need to force the values on a chart to calculate based on their domain.

Here is the Set Analysis for "_Vl Carteira", which basically excludes the sum when either DtPrej or DtPrejGer are smaller than DtRef

Sum({($-<DtPrej = {"$(= '<' & DtRef)"}> - <DtPrejGer = {"$(= '<' & DtRef)"}>)

*

<ProdutoFinanceiro -={'XYZ', 'ASD'},

DtRef = {"=Day(DtRef)=1"}>}

Saldo)

As you can see by the behavior below, the measure seems to calculate based on the max DtRef available (in this case 2017-Nov) However, it calculates correctly when only its own DtRef.YearMonth dimension is selected (matches the KPI object value).

Can anyone help me with the expression? Maybe something with Aggr()?

(And yes, I do realize that putting a flag on my script for the date comparison would be a better solution.)

measureecarteira.png

1 Solution

Accepted Solutions
sunny_talwar

Try this

Sum(Aggr(If(Alt(DtPrej, MakeDate(9999, 12, 31)) > DtRef and Alt(DtPrejGer, MakeDate(9999, 12, 31)) > DtRef and Day(DtRef) = 1, Sum(Saldo)), DtPrej, DtPrejGer, DtRef))

View solution in original post

9 Replies
sunny_talwar

Difficult to say what the exact expression needs to be, but I feel that you might need to use Aggr() function rather than set analysis because set analysis is evaluated once per chart and from what you are showing, it almost seems like you don't want that. You want dimension wise evaluation of set analysis, which is only possible using Aggr() function

william_fu
Creator II
Creator II
Author

Here's what I've tried, with the same results. Any ideas?


Sum(Aggr(

Sum({($-<DtPrej = {"$(= '<' & DtRef)"}> - <DtPrejGer = {"$(= '<' & DtRef)"}>)

*

<ProdutoFinanceiro -={'XYZ', 'ASD'},

DtRef = {"=Day(DtRef)=1"}>}

Saldo), DtRef))

sunny_talwar

You still have set analysis in there, I doubt that will work also

william_fu
Creator II
Creator II
Author

Can I not combine Aggr and Set Analysis?

I've been trying to follow these guidelines: Pitfalls of the Aggr function

How can I replicate my set analysis conditions without using set analysis?

sunny_talwar

You can, but for what you need, you need to convert your set analysis into Aggr()

william_fu
Creator II
Creator II
Author

Any hints to get me started?

sunny_talwar

Not sure what else to mention... more than I already did... May be if you share a sample, I might be able to help better

william_fu
Creator II
Creator II
Author

Hi Sunny,

See a sample app attached, with data and the measures.

In the graph, the values for 2017-Jul and 2017-Aug are ~40k lower than expected, since it is not considering PropID = 427242.

The KPI object displays the value correctly

Please let me know if you have any questions.

sunny_talwar

Try this

Sum(Aggr(If(Alt(DtPrej, MakeDate(9999, 12, 31)) > DtRef and Alt(DtPrejGer, MakeDate(9999, 12, 31)) > DtRef and Day(DtRef) = 1, Sum(Saldo)), DtPrej, DtPrejGer, DtRef))