Horizontal/Vertical Expressions in a Pivot Table

[Anonymous]

Hi Everybody,

I can't for the life of me figure out how to get one expression running vertically and one running horizontally. The expressions are:

Expr 1: Count({<DATE={'$(=DATE(vMinDate))'}>}DISTINCT(AccountID))

Expr 2: Count({<DATE={'$(=DATE(vMaxDate))'}>}DISTINCT(AccountID))

The labels (1/2/2014 and 7/1/2015) are calculated (=date($(vMinDate)) and =date($(vMaxDate))).

There are 2 dimensions, both of which are the same (MakeCategory). One is labeled "To Make" and the other "From Make".

I'm trying to show what accounts were held by a MakeCategory at a certain snapshot in time versus another. This should, in effect, show which accounts went from a MakeCategory, to another MakeCategory. I know this visualization may sound crazy, but it's my "absolutely necessary" requirement from the customer. I'd ideally like to have Expression 1 (1/2/2014) running vertically and have Expression 2 (7/1/2015) running horizontally. Trying to grab just one and drag/drop it in the vertical axis is ineffective. They both come with each other. Any ideas in order to separate these two expressions horizontally/vertically? If not, is there a better way that QlikView can display this requirement? Would a calculated dimension help solve this?

Thanks,

Ron

[marcus_sommer]

I'm not sure if it's possible in this way with a horizontal + vertically expression to calculate and display the historically movements from the accounts between your various categories.

I would rather try to build such data-structures within the script. I show within my data historically movements on a account-level but have no business reason to consolidate them in the kind you want.

For this I use expressions like:

...

count(mainID) as Count,

concat(StoreID, ' --> ', StoreID) as StoreHistory,

concat(Channel, ' --> ', StoreID) as ChannelHistory,

mid(concat(Channel, ' --> ', StoreID),

     index(concat(Channel, ' --> ', StoreID), ' --> ', - 2) + 5)) as LastMovement,

...

If you extend this to the dates of movements and extract it in further loads you could come closer to a solution but it will be not quite easy. I hope it will give you or others here some ideas.

- Marcus

[Anonymous]

Hi Marcus,

Thanks a lot for your insight. You got my wheels spinning here, for sure. The above solution would simply give the difference in account (AccountID field) holdings by the dimension, taking into consideration the snapshot dates selected. Am I following you correctly? This sounds perfect, if so. Will you please explain a little more about " index(concat(Channel, ' --> ', StoreID), ' --> ', - 2) + 5)) as LastMovement,"? I'm not quite sure what to make of the -2 or +5 arguments.

Thanks a lot,

Ron

[marcus_sommer]

The mid(index()) expressions is to get the last movement between the channels but in fact they is a bit longer with some if-loops then I showed here simplified. How it worked: it searched the last string-delimiter of ' --> ' and cut it out by increasing the index-number to the 5 chars of the delimiter.

But this a very old code where I duplicated my logic of ' --> ' in all expressions and which wouldn't be needed so complicated for the last movement. It could be done more eleantly with other functions like subfield(string, delimiter, -1).

- Marcus

[Anonymous]

Is it possible to consider just Snapshot date, AccountID, and MakeCategory for a script that would look at:

what MakeCategory was on Snapshot Date __/__/____ for AccountID 1234  ?

Essentially, it would then be possible to make a FromMakeCategory and ToMakeCategory column:

It would be some advanced scripting (I think) to have the FromMake be married to a selectable snapshot date, as well as for ToMake.

the resulting table would certainly have to end up looking like:

AccountID	FromMake	ToMake	Snapshot Date
1	EZ Car	EZ Car	2/1/2014
1	EZ Car	Yamama	5/1/2014
2	Clubbin Car	Clubbin Car	5/1/2014

Hence, the rows would duplicate to reflect the snapshot date for FromMake and ToMake (FromMake being 2/1/2014 and ToMake being 5/1/2014). A count aggregated on AccountID would get the numbers we're after for the pivot table. FromMake and ToMake being the Dimensions.

Any thoughts on how to proceed with such a script? Not positive if a For loop would be appropriate or any other useful functions().

Thanks,

Ron