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: 
timanshu
Creator III
Creator III

Using Dimension as filter in Expression.

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

4 Replies
Gysbert_Wassenaar

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)


talk is cheap, supply exceeds demand
timanshu
Creator III
Creator III
Author

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.

Gysbert_Wassenaar

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?


talk is cheap, supply exceeds demand
timanshu
Creator III
Creator III
Author

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.