Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
nevilledhamsiri
Specialist
Specialist

Can the set Expression be used to show somthing > or <

Hi,

As per the data presented below, could you show me how records of which  ACT_CUM>BUD_CUM   be shown using Set expression taking in to account the related dimensions! related data are as follows!

Thanks

Neville

      

BR_CODECATEGORYNOTE_CODETYPEACT_CUMULATIVEBUD_CUMULATIVEVARIANCE
RASTAFF EXPENSES500STAFF SALARIES7,507,077.417,480,400.00-26,677.41
RASTAFF EXPENSES513STAFF WELFARE242,390.18224,000.00-18,390.18
RASELLING EXPENSE702BUSINESS PROMOTION259,798.33265,000.005,201.67
EMSTAFF EXPENSES500STAFF SALARIES8,500,000.007,480,400.00-1,019,600.00
EMSTAFF EXPENSES513STAFF WELFARE350,000.00500,000.00150,000.00
EMSELLING EXPENSE702BUSINESS PROMOTION259,798.33229,800.00-29,998.33
1 Solution

Accepted Solutions
Frank_Hartmann
Master II
Master II

Sorry, Im not getting you.

please explain why the neg Variance -26677.41 should not be correct?

Both conditions are fullfilled:

the actValue > BudValue

and Variance <0


try this:

SUM({< VARIANCE = {"=VARIANCE<0"},ACT_CUMULATIVE = {"=sum(aggr(sum(ACT_CUMULATIVE),CATEGORY,BR_CODE,NOTE_CODE,TYPE)) > sum(aggr(sum(BUD_CUMULATIVE),CATEGORY,BR_CODE,NOTE_CODE,TYPE))"}>} ACT_CUMULATIVE)


Maybe you will have to include your specific dimensions into the aggregation function inside set analysis in order to get it work!


See attached File

View solution in original post

10 Replies
Frank_Hartmann
Master II
Master II

Maybe something like that:

sum({<ACT_CUMULATIVE={'>$(=BUD_CUMULATIVE)'}>}ACT_CUMULATIVE)

hope this helps

nevilledhamsiri
Specialist
Specialist
Author

Dear Frank,

Once applied it answer wont come. See the expression & chart below!

SUM({<ACT_CUMULATIVE={'>$(=BUD_CUMULATIVE)'}>}ACT_CUMULATIVE)

Thanks

Neville

Frank_Hartmann
Master II
Master II

try this:

SUM({< ACT_CUMULATIVE = {"=ACT_CUMULATIVE > BUD_CUMULATIVE"}>}ACT_CUMULATIVE)

Frank_Hartmann
Master II
Master II

OR:

SUM({< ACT_CUMULATIVE = {"=sum(aggr(sum(ACT_CUMULATIVE),CATEGORY,BR_CODE)) > sum(aggr(sum(BUD_CUMULATIVE),CATEGORY,BR_CODE))"}>} ACT_CUMULATIVE)

nevilledhamsiri
Specialist
Specialist
Author

Dear Frank,

Latest expression did work! Thanks

Neville

Can you also  please propose a suggestion to exclude something being shown in the table. Like what you highlighted the negative variance, is there a way where we could exclude positive records being appeared from the table.

Regrds

Neville

Frank_Hartmann
Master II
Master II

try this:

SUM({< VARIANCE = {"=VARIANCE<0"},ACT_CUMULATIVE = {"=sum(aggr(sum(ACT_CUMULATIVE),CATEGORY,BR_CODE)) > sum(aggr(sum(BUD_CUMULATIVE),CATEGORY,BR_CODE))"}>} ACT_CUMULATIVE)

nevilledhamsiri
Specialist
Specialist
Author

Dear Frank,

That produce the former results but what is not negative yet to be there without being excluded!

Thanks

Neville

Frank_Hartmann
Master II
Master II

If I correctly understood you then you want to show Result where

ACT_CUMULATIVE > BUD_CUMULATIVE

and the

VARIANCE < 0

Unbenannt.png

If this is not what you want then please show expected Output

nevilledhamsiri
Specialist
Specialist
Author

Dear Frank,

Please check the negative variance comes as 7507077 seems to be incorrect! Also with all other expression, the last expression written, all rows are yet to be seen!

thnks

Neville