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

Graph Against Months With Two Different Fields

I have a table for purchases and a table for upgrades. Both tables have their own date fields like this: PurchaseMonth and UpgradeMonth. What I would like to do is show a graph that sums both Purchases and Upgrades across months. For the dimensions field i'm using PurchaseMonth. Here's what I have for the expression:

Sum( PurchaseSales ) + Sum( {$<UpgradeMonth=PurchaseMonth>} UpgradeSales)

The problem i'm having is the UpgradeSales aren't getting added into the chart just the Purchase sales. I believe this is because I am using PurchaseMonth as the dimension. I've been messing around with the set analysis with no luck. I'm thinking I might need to create some sort of month table that isn't linked to either sales or purchases then graph against that value. Not sure how to do that though.

Thanks!

4 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

Moshe

The problem is that the set analysis is applied to the whole data set (ie before the dimensions are applied). So the second part of your expression is looking for upgrades in the same month as the purchase.

I think your best bet is to use a date island, and link sales & upgrades with Sum..if statements, like this...

Dimension: the month from the date island (eg diMonth).

Expression: Sum(If(PurchaseMonth = diMonth, Sales)) + Sum(If(UpgradeMonth = diMonth, UpgradeSales))

For more info on setting up date islands, do a search on this site for - you should be able to find several examples that will help you.

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Hi Jonathan,

Thanks for replying. I have tried your solution and i do get good numbers. My only problem is it wont display all months at one time. I created a diMonth list box and the chart only displays anything when a selected a particular month in this listbox. Any ideas?

jonathandienst
Partner - Champion III
Partner - Champion III

Moshe

Rather than selecting diMonth from a list box, have you tried using it as a chart dimension?

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
bobdawes
Contributor III
Contributor III

Jonathan, thanks for your post, specifically noting that set analysis is applied to the whole data set (ie before the dimensions are applied).   The Sum(if(field=dimension) alternative is beautiful