Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
I have an expression that doesn't seem to work with my months dimension. It will only display the value when a single month is selected. How could I make a chart where multiple months values for this expression are shown?
=SUM(AGGR(count({(<terminationdate = {">=$(=only(MonEnd3))"},
employmentdate ={"<=$(=only(MonEnd3))"}>)+(<terminationdate = {'-693593'},
employmentdate ={"<=$(=only(MonEnd3))"}>)-<terminationdate = {'-693593'},
employmentdate ={'-693593'}>}
distinct PRMNAM),PRMDDP))
Hi Brandon,
Can you word out what you're trying to achieve in this expression?
Yes, I'm summing the counts of distinct employee names aggreagated over the departments(PRMDDP)
where termindate is greater than the month end date and employmentdate is less than the month end date,
plus where termindate is null and employmentdate is less than the month end date,
minus where termindate is null and employmentdate is null.
MonEn3 field is probably linked to the month you are selecting in. Since you are using only(MonEn3), you only get a result if MonEn3 is unambiguous, which it is not if you select multiple month. Try using min(MonEn3) or max(MonEn3), whatever is appropriate for your requirement.
That is true swuehl, the issue is the ambiguity, but if I want a graph to show 12 months back, do you have any ideas how I would try something like that? Monthend3 provides the end of month date for the month selected. It works for any one month I select.
But in a graph useing that as an expression (with min or max even) and monthend3 as the dimension just gives me one months count still (the min or max) of the select or nothing if no selection for the month.
I can make 12 different expressions and then get the toals in a striaght chart but how would I turn that into a line chart?
Ah, your set analysis should regard your current month dimension value, sorry, I missed that (and sorry again, set analysis is evaluated once per chart, so a single set expression won't consider current dimension value. See
http://community.qlik.com/docs/DOC-1335 for a discussion and a possible workaround).
Golly swuehl, that might be a bit advanced explaination for me. Can you maybe give a simple example of how I might do that with what I have for expressions and dimensions?