Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Partner - Creator

## Mixing Standard and Synthetic Dimensions in the same Table

Hi,

I need to create a single table with a synthetic dimension and a standard dimension.

To be  clear: Let's say I need to show the total sales of last year in the first column, total sales of actual year in second column, and the following columns come from the "Month/Year" field and show the sales in each month of the selected values in "Month/Year" field.

The first 2 columns will always be there, but the Month/Year columns depend on the field selection. I thought about using the field "Month/Year" inside the ValueList() function, but I couldn't make it work properly.

Could someone help me with this one?

1 Solution

Accepted Solutions
MVP

Look at the attached solution may be

6 Replies
MVP

Look at the attached solution may be

Partner - Creator
Author

That's perfect, thanks a lot! You saved me a couple hours in just 3 lines.

Partner - Creator
Author

Sunny,

Under each "Month/Year", I need 2 expressions to be calculated: sum(Sales Amount) and sum(Sales Quantity).

In the columns  "Last Year Sales" and "This Year Sales"  these expressions shouldn't be calculated, only sum(Sales) as you did it.

Is it possible?

Contributor III

@sunny_talwar any chance that you explain how you did it instead of sharing a qvw file?

I'm using Qlik Sense and not Qlik View so I cannot open your file unfortunately.

Thanks a lot

MVP

I am not entirely sure what you are looking to get, but for this post, I did this

1) Created an Island Table in the script

``````Dim:
Dim
1
2
3
];``````

2) Created a chart with the following

Dimension

``=Pick(Dim, 'Last Year Sales', 'This Year Sales', Date(MonthYear, 'MMM-YYYY'))``

Expression

``=Pick(Only({1}Dim), Sum({1<Year = {"\$(=Max({1}Year) - 1)"}>}Sales), Sum({1<Year = {"\$(=Max({1}Year))"}>}Sales), Sum(Sales))``
Contributor III

@sunny_talwar thanks a lot!

Community Browser