topic Re: Mixing Standard and Synthetic Dimensions in the same Table in QlikView

Mixing Standard and Synthetic Dimensions in the same Table

lcontezini — Wed, 13 Sep 2017 18:55:16 GMT

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?

Re: Mixing Standard and Synthetic Dimensions in the same Table

sunny_talwar — Wed, 13 Sep 2017 19:21:22 GMT

Look at the attached solution may be

Re: Mixing Standard and Synthetic Dimensions in the same Table

lcontezini — Wed, 13 Sep 2017 19:35:01 GMT

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

Re: Mixing Standard and Synthetic Dimensions in the same Table

lcontezini — Wed, 13 Sep 2017 20:41:26 GMT

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!

Re: Mixing Standard and Synthetic Dimensions in the same Table

max31 — Wed, 06 Nov 2019 14:12:07 GMT

@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

Re: Mixing Standard and Synthetic Dimensions in the same Table

sunny_talwar — Thu, 07 Nov 2019 13:09:38 GMT

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))

Re: Mixing Standard and Synthetic Dimensions in the same Table

max31 — Wed, 04 Dec 2019 13:28:15 GMT

@sunny_talwar thanks a lot!