14 Replies Latest reply: Oct 24, 2016 3:13 PM by Sunny Talwar

# Modelling, Calculated values

Hi All,

I have been struggling to find a solution for a problem which is explained in the attached QVW file here.

It is something to do with calculating and storing values by ignoring few joins. Please suggest me a way to resolve this.

Thanks.

• ###### Re: Modelling, Calculated values

May be you can do like this in your expression:

=Sum({<FISC_YR_NBR={"\$(=vFXPrevYrToday)"}, CTRY_NAM, FROM_CCY_CD = p(FROM_CCY_CD)>}RATE_AMT)

=Sum({<FISC_YR_NBR={"\$(=vFXPrevYrToday)"},FISC_DAY_OF_YR_NBR={"<=\$(=Only({1}FXDayOfYearToday))"}, CTRY_NAM, FROM_CCY_CD = p(FROM_CCY_CD)>}RATE_AMT)

So, basically ignore selection in Country field and allow currency to drive the expressions.

• ###### Re: Modelling, Calculated values

Thanks for your solution Sunny, this is what i was looking for.

So if i want to change the Dimension to a different column from a different table, what should be done?

The values changes back to 23.9161 for Australia if the FISC_MTH_NBR is pulled from the DT_CONS_DIM table.

DT_CONS_DIM.FISC_MTH_NBR Sum1 Sum2 Days1 Days2
273.6691 221.1322 35 35
323.916123.91613535
420.16120.1612828
519.722819.72282828
622.348322.34833131
187.5209134.9843535

My intention is to get the Sum1, Sum2 values from the BB_CCY_EXCH_DIM table , but the table or graph should be plotted against the Fisc_Month_Number from DT_CONS_DIM table.

I could not find a way to avoid it, thats the reson i have included the FISC_MTH_NBR and other columns in the BB_CCY_EXCH table.

• ###### Re: Modelling, Calculated values

Hello,

I am trying to find a way to calculate values for Year To date, QTD and MTD. Requirement is below,

YTD: (Should be displayed in a Text box as a standalone value)

(Sum of Revenue of every Month)*(Average of Exchange of every month)

Average of Exchange of every month = (Sum of Exchange rates)/(No of days in that month)

If i build a table with Month as the dimension, i will be able to get the values, however if i remove it, everything rolls up which will not be the desired value for me.

 Month Revenue Sum of Exch rates No of Days Average Final Value 1 0 22.6158 28 0.8077 0 2 0 21.8186 28 0.3896 0 3 126134251 27.0161 35 0.2969 97361587 4 102366496 21.7015 28 0.1824 79339518 5 97645207 22.1012 28 0.1503 77074152 6 158756246 26.9398 35 0.1480 122196043 7 0 20.6823 28 0.0985 0 8 0 20.5046 28 0.0862 0 9 144221390 24.7217 35 0.0906 101868513 10 161623 13.7533 19 0.0471 116992

What needs to be done:

I need suggestions to achieve the below,

YTD  Final value should be in a single cell, NOT in a table format.

And it should be calculated as Sum of Monthly "Final value", which is Calculating Monthly Averages and multiplying them with Revenue every month and at the end, all the Final values has to be summed up.

Similar to this, the QTD should also be done.

Attached a sample application with this, please let me know if more details are needed.

Thanks.

• ###### Re: Modelling, Calculated values

Are these the numbers you are hoping to see?

• ###### Re: Modelling, Calculated values

Thanks a ton Sunny, I was trying out the Aggr but missed to place it at the place like you have done.

YTD is perfect, and QTD i have made it to perfection now.Just that we will have to choose a different Start date for Quarter, which you might not have a possibility of knowing.

Thanks again

• ###### Re: Modelling, Calculated values

Not a problem Ganesh -

If you got all the things you were looking for, I would suggest closing this thread by marking correct and any helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

• ###### Re: Modelling, Calculated values

Getting into multiple issues when i explore more on this.

Now if i bring in another dimension and try to plot the values against them, it is not giving me the right numbers.

Set expression being used: (Please ignore the first two "if" conditions as they are working as expected.)

=Num(if(vCurr='USD', Sum({<PTY_VEND_SUP_GP_DES=,PTY_VEND_GP_DES=,PTY_VEND_SUB_GP_DES=,FISC_YR_NBR={"\$(=Only({1}YearofToday))"},DateNum={">=\$(=Num(FISC_YR_START_DT))<=\$(=Num(Today()))"}>} PRC_USD_AMT)

,if(vCurr='LC',Sum({<PTY_VEND_SUP_GP_DES=,PTY_VEND_GP_DES=,PTY_VEND_SUB_GP_DES=,FISC_YR_NBR={"\$(=Only({1}YearofToday))"},DateNum={">=\$(=Num(FISC_YR_START_DT))<=\$(=Num(Today()))"}>} PRC_LCY_AMT)

,if(vCurr='FXN',Sum(Aggr(Sum({<FISC_YR_NBR={"\$(=Only({1}YearofToday))"},DateNum={">=\$(=Num(FISC_YR_START_DT))<=\$(=Num(Today()))"}>} PRC_LCY_AMT)

*(Sum({<FISC_YR_NBR={"\$(=vFXPrevYrToday)"},FISC_DAY_OF_YR_NBR={"<=\$(=Only({1}FXDayOfYearToday))"},CTRY_NAM, FROM_CCY_CD = p(FROM_CCY_CD)>}RATE_AMT)

/Max({1<FISC_YR_NBR={"\$(=vFXPrevYrToday)"},FISC_DAY_OF_YR_NBR ={"<=\$(=Only({1}FXDayOfYearToday))"}>}FISC_DAY_OF_MTH_NBR)), FISC_MTH_NBR))))),'#,###,##0')

The list should look like the above image, with a list of names, but it gets reduced like below.

Even though the list has shrunk, the Total values are matching with the YTD values. And the 'Others' value is always zero.

The names are from a new dimension table and it is connected to the SLS_RPT_TRNS table with a key value.Sorry, i could not include the new dimension in a sample here. Please let me know if you need more information.

• ###### Re: Modelling, Calculated values

Not sure I understand it completely, but have you add your new dimension to the Aggr() function? Any dimension you add to your chart, add it to your Aggr() function and the fact that you are using some if statements could make the mismatch even more bigger if you don't add all your dimensions to the Aggr() function.

• ###### Re: Modelling, Calculated values

Attaching the sample qvw file. I have included the new dimension into the AGGR expression. But the Sum of rates is also chaning if i pull in everything into a table with the new dimension. Let me validate the backend of data.

Found out the issue.The Rate amount is not getting calculated independent of the new dimension. It is getting related to the New dimension and its entries in the Fact, so it is filtered out. The difference in value is because of that, i am trying out the below Set expressions but no luck yet.

How do i exclude the dimension even if i am using the dimension in my chart/table?

if(vCurr='FXN',Num(Sum(Aggr(Sum({<FISC_YR_NBR={"\$(=Only({1}YearofToday))"},DateNum={">=\$(=Num(FISC_YR_START_DT))<=\$(=Num(Today()))"}>} PRC_USD_AMT)

*(Sum({<FISC_YR_NBR={"\$(=vFXPrevYrToday)"},FISC_DAY_OF_YR_NBR={"<=\$(=Only({1}FXDayOfYearToday))"},PTY_VEND_GP_DES, CTRY_NAM, FROM_CCY_CD = p(FROM_CCY_CD)>}RATE_AMT)

/Max({1<FISC_YR_NBR={"\$(=vFXPrevYrToday)"},FISC_DAY_OF_YR_NBR ={"<=\$(=Only({1}FXDayOfYearToday))"}>}FISC_DAY_OF_MTH_NBR)),PTY_VEND_GP_DES, FISC_MTH_NBR)),'#,###,###,##0')))

• ###### Re: Modelling, Calculated values

What is the output that you are expecting to see? Can you elaborate on this?

• ###### Re: Modelling, Calculated values

I have uploaded the sample application with added dimensions, the numbers when USD or FXn buttons are selected should be the same in the 'Actual' table. My Set expressions for PY which is Prior Year is very similar to the Current Year CY.

• ###### Re: Modelling, Calculated values

This expression seems to have worked for me

=Num(Sum(Aggr(if(vCurr='USD',

Sum({<FISC_YR_NBR={"\$(=(vPrevYrToday))"},FISC_DAY_OF_YR_NBR={"<=\$(=Only({1}DayOfYearToday))"}>} PRC_USD_AMT),

if(vCurr='FXN',Sum(Aggr(Sum({<FISC_YR_NBR={"\$(=(vPrevYrToday))"},FISC_DAY_OF_YR_NBR={"<=\$(=Only({1}DayOfYearToday))"}>} PRC_LCY_AMT)

*(Sum(TOTAL<FISC_MTH_NBR>{<FISC_YR_NBR={"\$(=vFXPrevYrToday)"},FISC_DAY_OF_YR_NBR={"<=\$(=Only({1}FXDayOfYearToday))"},

CTRY_NAM,PTY_VEND_SUP_GP_DES,PTY_VEND_GP_DES,LOB_LVL1_DES,LOB_LVL2_DES,LOB_LVL3_DES, FROM_CCY_CD = p(FROM_CCY_CD)>}RATE_AMT)

/Max(TOTAL<FISC_MTH_NBR>{1<FISC_YR_NBR={"\$(=vFXPrevYrToday)"}>}FISC_DAY_OF_MTH_NBR)),

PTY_VEND_SUP_GP_DES,PTY_VEND_GP_DES,LOB_LVL1_DES,LOB_LVL2_DES,LOB_LVL3_DES, FISC_MTH_NBR)))), PTY_VEND_GP_DES)),'#,###,###,##0')

• ###### Re: Modelling, Calculated values

The numbers when we switch between USD and FXn should be the same for the country "US" and it is working as expected for the CY column. Please refer the screens below, the CY column will not change if we switch between USD and FXN which is right. It is deviating only for the PY column here. Moreover what will happen if i introduce a cyclic dimension to this table? Should it br grouped on all the different dimension of the group?

• ###### Re: Modelling, Calculated values

So this is more like an issue with your current expression in the straight table also? Because based on my selection in USD OR FXn, I am seeing the numbers changing in the straight table also. Which of the two numbers are the correct numbers? 7,900,... or 8,035,...?

You can handle Cycle Group using GetCurrentField(GroupName) within the dollar sign expansion

\$(='[' & GetCurrentField([GroupName]) & ']') in place of PTY_VEND_GP_DES in the Aggr() expression above