7 Replies Latest reply: Jan 6, 2017 5:39 AM by Sunny Talwar

# Is it possible to apply condition on the dimension within the expression?

Hi,

I am using the below expression in a bar chart where the dimension is BMonth, nothing but month(BDate).

Count({<BType={'Scheduled','Completed'},COMPLETED-= {'<\$(=num(MonthStart(Max(BDate))))'}>
+
<
BType={'Scheduled'}, SCHEDULED={'<\$(=num(Max(BDate)+1)))'}>
}
distinct CODE
)

I am trying to write expression where :

COMPLETED-= {'<\$(=num(MonthStart(Max(BDate))))'} should consider all the CODEs which was completed in jan and after jan (on the bar "Jan") - completed in jan 2016, feb 2016, mar 2016....

SCHEDULED={'<\$(=num(Max(BDate)+1)))'}> should consider all the CODEs which was scheduled before jan (on the bar "Jan") - scheduled in dec 2015, nov 2015....

Thank you very much.

• ###### Re: Is it possible to apply condition on the dimension within the expression?

It might be helpful if you can share few rows of data and explain what exactly you are looking to get as your expected output

• ###### Re: Is it possible to apply condition on the dimension within the expression?

Hi Sunny,

I have attached the sample document below.

This is what I am trying to achieve -

The 3 conditions on extreme right column is what I am trying to achieve using the expression

Count({<BType={'Targeted','Completed'},COMPLETED-= {'<\$(=num(MonthStart(Max(BDate))))'}>
+
<
BType={'Targeted'}, TARGET={'<\$(=num(Max(BDate)+1)))'}>
}
distinct CODE
)

As shown in the picture, in the bar chart for the bar "Jan" the value should be 6.

I hope this information is sufficient, incase you need more info kindly let me know.

Thank you very much for the response.

• ###### Re: Is it possible to apply condition on the dimension within the expression?

Here is two different versions of expression which gets close, but not entirely there

I am not sure I can get 972828 in there because right not it is only associated with BMonth = Dec.... This might be needed to be resolved in the script. I don't know why I am getting 1053685 to be included in Jan. I am not getting time to dig deeper, but this should give your some direction.

Best,

Sunny

• ###### Re: Is it possible to apply condition on the dimension within the expression?

HI Sunny,

Your response steered me into the right direction.

Thank you.

• ###### Re: Is it possible to apply condition on the dimension within the expression?

So is this resolved now? What solution did you end up using? Would you share your solution for other users who might have similar issue in the future?

Also, please close this thread down by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

• ###### Re: Is it possible to apply condition on the dimension within the expression?

Sure, I am still working on the expression. Once I have it right, I shall post here.

Thank you.

• ###### Re: Is it possible to apply condition on the dimension within the expression?

Awesome, thanks!!