Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
sona_sa
Creator II
Creator II

Expression Help

Hi,

Can anyone help me on below expression.

I have my expression - sum(aggr( Fabs((Sum(SALES_QTY) - Sum(SOP_Cts))/1000), CATEGORY))

Now With some condition I have to change in

If(Year ='2014',(sum({$<Year = {2013,2014},MONTH = {"<=$(vMonth)"},HalfYear,Quarter>} SALES_AMT_USD/1000000)),

If(Year = '2013',(sum({$<Year={2013},MONTH = {7,8,9,10,11,12},HalfYear,Quarter>}SALES_AMT_USD)/1000000),

(Sum(SALES_AMT_USD)/1000000)))

Previous one I have to convert in

If(Year ='2014',(sum({$<Year = {2013,2014},MONTH = {"<=$(vMonth)"},HalfYear,Quarter>} SALES_AMT_USD/1000000)),

If(Year = '2013',(sum({$<Year={2013},MONTH = {7,8,9,10,11,12},HalfYear,Quarter>}SALES_AMT_USD)/1000000),

(Sum(SALES_AMT_USD)/1000000)))

Thanks

11 Replies
MK_QSL
MVP
MVP

What you want ?

Not applicable

Agree with Manish, not sure what you are asking here!

sona_sa
Creator II
Creator II
Author

I want the same aggr function whatever I am using in 1st expression i.e - sum(aggr( Fabs((Sum(SALES_QTY) - Sum(SOP_Cts))/1000), CATEGORY))

Same should be in - sum({$<Year = {2013,2014},MONTH = {"<=$(vMonth)"},HalfYear,Quarter>} SALES_AMT_USD/1000000))

MK_QSL
MVP
MVP

Can you provide sample data or sample application?

sona_sa
Creator II
Creator II
Author

Yes Please find the attached xls.

for Actual - 2014 Data is available till Mar and for 2013 - from Jul to Dec and Plan is available for 2013 from JAN to DECEMBER, I want YTD. By Category wise with (Acual - Plan)/1000

sona_sa
Creator II
Creator II
Author

Yes Please find the attached xls.

for Actual - 2014 Data is available till Mar and for 2013 - from Jul to Dec and Plan is available for 2013 from JAN to DECEMBER, I want YTD. By Category wise with (Acual - Plan)/1000

MK_QSL
MVP
MVP

Create a Pivot Table

Dimension

CATEGORY

YEAR

Expression

FABS(SUM(Actual)-SUM(Plan))/1000

This is what you are looking for?

sona_sa
Creator II
Creator II
Author

I want diff. between Actual - Plan

sum(Aggr( Fabs(Sum({$<Year = {2014},MONTH = {"<=$(vMonth)"}>}((SALES_QTY)-(SOP_Cts))/1000)),CATEGORY))

For Output please find the attached

sona_sa
Creator II
Creator II
Author

I want diff. between Actual - Plan

sum(Aggr( Fabs(Sum({$<Year = {2014},MONTH = {"<=$(vMonth)"}>}((SALES_QTY)-(SOP_Cts))/1000)),CATEGORY))