Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Qvmaster2019
Creator
Creator

Synthetic Fields as Dimension

Using the same synthetic fields as dimensions, I have this.  It is like "1:Many".

#1

                             

=ValueList('John','Mary','Joseph')

=ValueList('John','Mary','Joseph')

1

John

John

1

John

Mary

1

John

Joseph

1

Mary

John

1

Mary

Mary

1

Mary

Joseph

1

Joseph

John

1

Joseph

Mary

1

Joseph

Joseph

1

But what I want is this (more like 1:1) :

#2

           

=ValueList('John','Mary','Joseph')

=ValueList('John','Mary','Joseph')

1

John

John

1

Mary

Mary

1

Joseph

Joseph

1

I can achieve #2 if I move one of synthetic fields to become expression but I need both to be in dimension.

Another restriction,  values inside the valuelist function will become dynamic later on depending on various selection. This means I cannot do this in load script.

Anyway to achieve this?

15 Replies
Qvmaster2019
Creator
Creator
Author

Current requirements: The output required is a pivot table with :

Dim1:Top N Countries based on Sales on selected Month.

Dim2: Year 1 and Year 2 (based on user selection)

DIm 3: Periods in the form of 4 Quarters Ago, 3 Quarters Ago, 2 Quarters Ago, 1 Quarter Ago, YTD, MTD, Previous Month, 2months ago appearing at the same time where Year depends on Dim2

Dim 4: Bucketing of Sales: e.g. All Range, 0-$300, 300-600, 0-600. Range can be dynamic based on user input.

Dim 5: Materials  in this format:

Material 1

Material 2

Material 3

Material 4

Material 5

Material 6

Material 7

Where Material 1 is the latest Material for that country

Material 2 is the top selling Material for that country except Material 1

Material 3 is the top selling Material for that country except Material 1 and Material 2

Material 4 is the TOTAL of all Materials flagged as "Special"

Material 5 is the top Material flagged as "Special" but not Material 1, Material 3 and Material 4

Material 6 is the 2 top Material flagged as "Special" but not Material 1, Material 3 and Material 4 and Material 5

Material 7 is the top 1 Material NOT flagged as "Special" but not Material 1, Material 3 and Material 4

Everything is working with valuelist() then comes Dim6 as the new requirement

Dim6: Categorized Dim3 if "Quarterly", "Yearly" or "Monthly"

Qvmaster2019
Creator
Creator
Author

I'm looking for one that will not touch the expression anymore. Or any alternative solution that I can play with.

ganeshsvm
Creator II
Creator II

Can you post some sample data in Excel?

vinieme12
Champion III
Champion III

can you post sample app/ sample data?

Preparing examples for Upload - Reduction and Data Scrambling

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Qvmaster2019
Creator
Creator
Author

Data is nothing fancy. Very straight forward.

   

CountryModelTypePeriod Sales
USAAASpecial04 18 2017 $               98,389
USBBBRegular04 18 2017 $               11,229
USAAASpecial04 18 2016 $               29,084
USBBBRegular04 18 2016 $               37,980
AUAAASpecial04 18 2017 $               79,867
AUCCCRegular04 18 2017 $               15,469
AUAAASpecial04 18 2016 $               11,328
AUDDDRegular04 18 2016 $               61,720
Qvmaster2019
Creator
Creator
Author

Data is nothing fancy. Very straight forward.

   

CountryModelTypePeriod Sales
USAAASpecial04 18 2017 $               98,389
USBBBRegular04 18 2017 $               11,229
USAAASpecial04 18 2016 $               29,084
USBBBRegular04 18 2016 $               37,980
AUAAASpecial04 18 2017 $               79,867
AUCCCRegular04 18 2017 $               15,469
AUAAASpecial04 18 2016 $               11,328
AUDDDRegular04 18 2016 $               61,720