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: 
maxim1500
Partner - Creator
Partner - Creator

Set analysis and the calendar table

Hello,

I have different charts using dates differently. For example, I have some bar charts displaying total values for months, years, quarters, etc. Each of them use a column from the calendar table as a dimension.

I also have a scatterplot displaying trailing 12 months revenues. The date is filtered using set analysis:

Sum({<MonthlyFact.SnapshotDate = {"<=$(=max(num(MonthlyFact.SnapshotDate)))"}*{">=$(=num(addyears(max(MonthlyFact.SnapshotDate),-1)))"}>}[AccountMonthly.FirmCurAdvisorRevenue])

Since the filtering is not done on the same field [MonthlyFact.SnapshotDate] vs calendar table columns, I loose my trailing 12 months selection if I click on a single month. If I replace the calendar columns with SnapshotDate in my bar charts, I can select a date and the trailing starts at that date. This is the expected behavior.

Is there a way to be able to display months, quarters and years on some charts and still be able to use set analysis?

Thank you!

1 Solution

Accepted Solutions
sunny_talwar

May be this:

Sum({<MonthlyFact.SnapshotDate = {"<=$(=max(num(MonthlyFact.SnapshotDate)))"}*{">=$(=num(addyears(max(MonthlyFact.SnapshotDate),-1)))"}, CalanderDateField, CalanderMonthField, CalanderQuarterField, CalanderYearField>}[AccountMonthly.FirmCurAdvisorRevenue])

View solution in original post

3 Replies
sunny_talwar

May be this:

Sum({<MonthlyFact.SnapshotDate = {"<=$(=max(num(MonthlyFact.SnapshotDate)))"}*{">=$(=num(addyears(max(MonthlyFact.SnapshotDate),-1)))"}, CalanderDateField, CalanderMonthField, CalanderQuarterField, CalanderYearField>}[AccountMonthly.FirmCurAdvisorRevenue])

maxim1500
Partner - Creator
Partner - Creator
Author

Indeed! Thank you very much for your time!

sunny_talwar

Not a problem