Mixing Standard and Synthetic Dimensions in the same Table

[lcontezini]

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?

[sunny_talwar · Accepted solution]

Look at the attached solution may be

[lcontezini]

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

[lcontezini]

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]

@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]

@sunny_talwar thanks a lot!