7 Replies Latest reply: Aug 24, 2012 12:56 PM by brandon breitling

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

• ###### Re: Want to look at multiple months rather than only current selected for dimension

Hi Brandon,

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

• ###### Re: Want to look at multiple months rather than only current selected for dimension

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.

• ###### Re: Want to look at multiple months rather than only current selected for dimension

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.

• ###### Re: Want to look at multiple months rather than only current selected for dimension

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.

• ###### Re: Want to look at multiple months rather than only current selected for dimension

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?

• ###### Re: Want to look at multiple months rather than only current selected for dimension

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

• ###### Re: Want to look at multiple months rather than only current selected for dimension

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?