topic Re: Using Dimension as filter in Expression. in QlikView

Using Dimension as filter in Expression.

timanshu — Fri, 24 Apr 2015 16:10:09 GMT

I have a scenario where I want to create a bar chart where expression requires time duration in set analysis. But Dimension is also a time duration. How to solve this?

Here is Complete Scenario:

I have a Date field. Max Date is Maximum of that date. I want a dimension which always have has 3 values -

1.Maximum date available(Month end of last month in Quarter) in last quarter of Max date.

2.Maximum date available(Month end of last month in Quarter) in last to last quarter of Max date.

3.Max Date.

Calculating these is not a problem.

Now I want Sum of Sales corresponding to three dates in Dimension.Sum of sales corresponding to less that the available dates in dimension.

expression to understand can be sum( {<Date={'<=$(=Date)'}>} Sales) .

How to make this possible.?

Re: Using Dimension as filter in Expression.

Gysbert_Wassenaar — Fri, 24 Apr 2015 16:14:18 GMT

I'd create three variables with the expressions to calculate the three dates. Then use these three variables in a set analysis expression:

sum({<Date={ $(vMaxDate), $(vMaxDateOfQuarter), $(vMaxDateOfLastQuarter) }>}Sales)

Re: Using Dimension as filter in Expression.

timanshu — Fri, 24 Apr 2015 16:30:28 GMT

Hi Gysbert ,

I didn't get you. What will be the dimension then? to show 3 dates. And I want sales of all dates previous to dates in dimension.

Re: Using Dimension as filter in Expression.

Gysbert_Wassenaar — Fri, 24 Apr 2015 16:34:07 GMT

I'm probably misunderstanding what you're trying to achieve. Can you post an example with source data and the result table or graphic that should be shown?

Re: Using Dimension as filter in Expression.

timanshu — Fri, 24 Apr 2015 17:25:02 GMT

Hi Gysbert,

I cant post Data. But let provide u a clearer picture.

I want something like attached screenshot. Expression should solve the purpose as shown in screenshot.

I am creating dimension by individually calculating 3 Dates and concatenating them.

Like :-

If(Date = $(vLastQuarDate),Date) & If(Date = $(vLastToLastQuarDate),Date) & If(Date = $(vMaxDate),Date).

If you can give another way of creating required dimension I don't mind. But it should Solve the purpose.Let me know if you require anything.

Thanks.