topic Re: Modelling, Calculated values in QlikView

Modelling, Calculated values

Wed, 19 Oct 2016 18:58:01 GMT

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

sunny_talwar — Wed, 19 Oct 2016 19:17:04 GMT

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

Thu, 20 Oct 2016 06:20:25 GMT

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

Fri, 21 Oct 2016 11:58:05 GMT

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.

Re: Modelling, Calculated values

sunny_talwar — Fri, 21 Oct 2016 20:05:44 GMT

Are these the numbers you are hoping to see?

Re: Modelling, Calculated values

Fri, 21 Oct 2016 20:34:55 GMT

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 — Fri, 21 Oct 2016 20:40:26 GMT

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

Sat, 22 Oct 2016 13:24:22 GMT

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 — Sat, 22 Oct 2016 14:12:50 GMT

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

Sat, 22 Oct 2016 15:24:51 GMT

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

sunny_talwar — Mon, 24 Oct 2016 06:35:20 GMT

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

Re: Modelling, Calculated values

Mon, 24 Oct 2016 14:25:56 GMT

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

sunny_talwar — Mon, 24 Oct 2016 15:51:05 GMT

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

Mon, 24 Oct 2016 19:00:15 GMT

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 — Mon, 24 Oct 2016 19:08:39 GMT

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