Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to use two measures based on two different date fields in a single pivot table. There are several key fields in my data, but below is a simplified example of the data in my load script:
Table 1:
User Id
SalesMonth
Amount (amount sold in dollars)
Table 2:
User Id
QuotaMonth
Quota (sales goal in dollars)
The tables are joined on UserId, and renaming the month fields as the same field name creates a synthetic key. My goal is to have the Month going across the top of the pivot table, with Quota and Amount as measures beneath each month (so we can do a side-by-side comparison of sales quota vs. amount sold in a pivot table). Any help is appreciated!
HI Melissa,
you can join data based on as follows:
[User Id]&'_'&[SalesMonth] as %Key,
[User Id]&'_'&[QuotaMonth] as %Key,
join on this,
Create pivot table as below:
Jan | Feb | Mar | ||||
UserID | Sum(Sale) | Sum(Quota) | Sum(Sale) | Sum(Quota) | Sum(Sale) | Sum(Quota) |
1 | ## | ## | ## | ## | ## | ## |
2 | ## | ## | ## | ## | ## | ## |
3 | ## | ## | ## | ## | ## | ## |
4 | ## | ## | ## | ## | ## | ## |
Thanks,
Arvind Patil