Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
khanashique
Contributor II

More than 100 lines of Set Expression due to crosstable

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.

 

4 Replies
fosuzuki
Valued Contributor II

Re: More than 100 lines of Set Expression due to crosstable

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
Contributor II

Re: More than 100 lines of Set Expression due to crosstable

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.

MVP & Luminary
MVP & Luminary

Re: More than 100 lines of Set Expression due to crosstable

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
Contributor II

Re: More than 100 lines of Set Expression due to crosstable

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)