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: 
birchgold
Contributor II
Contributor II

Linking Dates Without Linking Entire Sheets

I can’t seem to figure out a way to link data together only for a specific chart.

Is there some kind of expression to put in the formula so that it can return a measure linked to what I ask it to.
For example, in a table where the rows are Date Added YearMonth and Publisher, Column is count Leads and sum Cost. I want to see Leads for those Date Added Year Month but Cost based on a different Date from a different data sheet, not Date Added Year Month.

If I make 2 separate tables it works fine.

I want to see, in the same table, Sum(cost) for End Date YearMonth and Count(Leads) for Date Added YearMonth.

When I make a chart and use dimension End Date YearMonth and those two measures, Leads is wrong.

Vice versa, if I make a chart and use dimension Date Added YearMonth and those two measures, it brings up the incorrect Cost.



I believe the comes from the link between the two sheets. The variable linking them is not distinct for a specific date, therefore it is aggregating all of it that contain that variable.  I cannot change the structure nor the link to these sheet. Want to find a way to work around it. This expression should also work for a Combo Chart that also looks at Leads, Cost, and Date Added.

2 Replies
karthiksrqv
Partner - Creator II
Partner - Creator II

Hi,

For the Cost you'll have to use a set analysis expression that fetches the appropriate date . If you use End Date, build the set on the Date, if you use Date, build the set on the End Date.

birchgold
Contributor II
Contributor II
Author

So for the table I used Date Added which has the correct Lead count but not sum Cost. So for the Cost measure i used this expression:

Sum({<[End date.autoCalendar.YearMonth] = [Date Added.autoCalendar.YearMonth]>} Cost)


It did not work. Any ideas why?

pivottable.JPG