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: 
Not applicable

Want to look at multiple months rather than only current selected for dimension

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

7 Replies
bgerchikov
Partner - Creator III
Partner - Creator III

Hi Brandon,

Can you word out what you're trying to achieve in this expression?

Not applicable
Author

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.

swuehl
MVP
MVP

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.

Not applicable
Author

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.

Not applicable
Author

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?

swuehl
MVP
MVP

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

Not applicable
Author

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?