Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
lcontezini
Partner - Creator
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
sunny_talwar

Look at the attached solution may be

Capture.PNG

View solution in original post

6 Replies
sunny_talwar

Look at the attached solution may be

Capture.PNG

lcontezini
Partner - Creator
Partner - Creator
Author

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

lcontezini
Partner - Creator
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?

Thanks in advance!

max31
Contributor III
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

sunny_talwar

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:
LOAD * Inline [
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))
max31
Contributor III
Contributor III

@sunny_talwar thanks a lot!