More than 100 lines of Set Expression due to crosstable

[khanashique]

Hi Experts,

I have more than 30 measures which has to be calculated row level, i have tried pivot table by putting the 30 measures expressions using "if" condition in one column below is the sample expression for 6 measures, but it has limitation on no.of lines/characters in set expression, Qliksense pivot only excepts somewhere around 8192 characters including spaces and which handles few measures only.

if([Category-Description]='Budgeted (Sales Revenue)',
SUM({<Category = {'Sales Revenue'}, YEAR = {"$(=YEAR(today()))"} >}Target),
if([Category-Description]='Budgeted (TON)',
SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target),
if([Category-Description]='Avg Selling P/TON Target',
SUM({<Category = {'Sales Revenue'}, YEAR = {"$(=YEAR(today()))"}>}Target)/SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target),
if([Category-Description]='Budgeted (Mtrl Cost)',
SUM({<Category = {'Material Cost'}, YEAR = {"$(=YEAR(today()))"}>}Target),
if([Category-Description]='Budgeted (DM Contribution)',
SUM({<Category = {'Sales Revenue'}, YEAR = {"$(=YEAR(today()))"}>}Target)/SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target)-SUM({<Category = 

{'Material Cost'}, YEAR = {"$(=YEAR(today()))"}>}Target)/SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target),
if([Category-Description]='Budgeted (Contribution %)',
((SUM({<Category = {'Sales Revenue'}, YEAR = {"$(=YEAR(today()))"}>}Target)/SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target)-SUM({<Category = 

{'Material Cost'}, YEAR = {"$(=YEAR(today()))"}>}Target)/SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target))/(SUM({<Category = {'Sales 

Revenue'}, YEAR = {"$(=YEAR(today()))"}>}Target)/SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target)))*100,
))))))

Can anyone advice me how do i handle all the measures in one measure either through load script/variable/master measures.

Thanks,

MAK.

[fosuzuki]

One way to save up a few characters is to use the Pick(Match()) combo instead of the nested IFs:

Pick(Match([Category-Description],'Budgeted (Sales Revenue)','Budgeted (TON)','Avg Selling P/TON Target','Budgeted (Mtrl Cost)','Budgeted (DM Contribution)','Budgeted (Contribution %)',
SUM({<Category = {'Sales Revenue'}, YEAR = {"$(=YEAR(today()))"} >}Target),
SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target),
SUM({<Category = {'Sales Revenue'}, YEAR = {"$(=YEAR(today()))"}>}Target)/SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target),
SUM({<Category = {'Material Cost'}, YEAR = {"$(=YEAR(today()))"}>}Target),
SUM({<Category = {'Sales Revenue'}, YEAR = {"$(=YEAR(today()))"}>}Target)/SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target)-SUM({<Category = {'Material Cost'}, YEAR = {"$(=YEAR(today()))"}>}Target)/SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target),
((SUM({<Category = {'Sales Revenue'}, YEAR = {"$(=YEAR(today()))"}>}Target)/SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target)-SUM({<Category = {'Material Cost'}, YEAR = {"$(=YEAR(today()))"}>}Target)/SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target))/(SUM({<Category = {'Sales Revenue'}, YEAR = {"$(=YEAR(today()))"}>}Target)/SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target)))*100
))

[khanashique]

Hi,

Thanks for your reply,

i believe here pickmatch will not work, because "Category-Description" is an inline load script and rest comes from transaction table.

Thanks,

MAK.

[marcus_sommer]

The suggestion from  fosuzuki will work in the same way like your nested if-loops - it's only a different and better way to handle multiple of such conditions.

Personally I would probably use another approach by extending the inline-table to another field which contained your filter values, like:

t: load * inline [
desc, value
Budgeted (Sales Revenue), Sales Revenue
...
];

and then you could grab this value - maybe with p() like:

SUM({<Category = p(value), YEAR = {"$(=YEAR(today()))"} >}Target)

- Marcus

[miskinmaz]

You can create an inline for the custom dimension used in value list. and in expression u can use the inline dim with pick match. Also u can store the set analysis exp in a variable and use the varaible like below:
sum({<$(vSetVar)>}sales)