Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
birchgold
Not applicable

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
Not applicable

Re: Linking Dates Without Linking Entire Sheets

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
Not applicable

Re: Linking Dates Without Linking Entire Sheets

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