topic Graph Against Months With Two Different Fields in QlikView

Graph Against Months With Two Different Fields

Mon, 29 Nov 2010 17:46:12 GMT

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!

Graph Against Months With Two Different Fields

jonathandienst — Mon, 29 Nov 2010 18:43:49 GMT

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

Graph Against Months With Two Different Fields

Mon, 29 Nov 2010 21:03:57 GMT

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?

Graph Against Months With Two Different Fields

jonathandienst — Tue, 30 Nov 2010 07:04:46 GMT

Moshe

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

Jonathan

Graph Against Months With Two Different Fields

bobdawes — Thu, 22 Sep 2011 16:06:23 GMT

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