
Re: Modelling, Calculated values
Sunny Talwar Oct 19, 2016 3:17 PM (in response to Ganesh Bagavathi)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
Ganesh Bagavathi Oct 20, 2016 2:20 AM (in response to Sunny Talwar )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 3 23.9161 23.9161 35 35 4 20.161 20.161 28 28 5 19.7228 19.7228 28 28 6 22.3483 22.3483 31 31 187.5209 134.984 35 35 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
Ganesh Bagavathi Oct 21, 2016 7:58 AM (in response to Ganesh Bagavathi)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)
What is already done:
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.

FXN_Forum.qvw 12.2 MB

Re: Modelling, Calculated values
Sunny Talwar Oct 21, 2016 4:05 PM (in response to Ganesh Bagavathi)
FXN_Forum (1).qvw 12.4 MB

Re: Modelling, Calculated values
Ganesh Bagavathi Oct 21, 2016 4:34 PM (in response to Sunny Talwar )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
Sunny Talwar Oct 21, 2016 4:40 PM (in response to Ganesh Bagavathi)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
Ganesh Bagavathi Oct 22, 2016 9:24 AM (in response to Sunny Talwar )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
Sunny Talwar Oct 22, 2016 10:12 AM (in response to Ganesh Bagavathi)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
Ganesh Bagavathi Oct 22, 2016 3:15 PM (in response to Sunny Talwar )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')))

FXN_Forum (1).qvw 31.3 MB

Re: Modelling, Calculated values
Sunny Talwar Oct 24, 2016 2:35 AM (in response to Ganesh Bagavathi)What is the output that you are expecting to see? Can you elaborate on this?

Re: Modelling, Calculated values
Ganesh Bagavathi Oct 24, 2016 10:25 AM (in response to Sunny Talwar )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.

FXN_Forum (2).qvw 59.6 MB

Re: Modelling, Calculated values
Sunny Talwar Oct 24, 2016 11:51 AM (in response to Ganesh Bagavathi)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')

FXN_Forum (2).qvw 56.2 MB

Re: Modelling, Calculated values
Ganesh Bagavathi Oct 24, 2016 3:00 PM (in response to Sunny Talwar )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?
Thanks in advance Sunny.

Re: Modelling, Calculated values
Sunny Talwar Oct 24, 2016 3:13 PM (in response to Ganesh Bagavathi)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










