Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

14 Replies
sunny_talwar

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.

Capture.PNG

Not applicable
Author

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.FXN_Forum.JPG

Not applicable
Author

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.

MonthRevenueSum of Exch ratesNo of DaysAverageFinal Value
1022.6158280.80770
2021.8186280.38960
312613425127.0161350.296997361587
410236649621.7015280.182479339518
59764520722.1012280.150377074152
615875624626.9398350.1480122196043
7020.6823280.09850
8020.5046280.08620
914422139024.7217350.0906101868513
1016162313.7533190.0471116992

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.

sunny_talwar

Are these the numbers you are hoping to see?

Capture.PNG

Not applicable
Author

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

sunny_talwar

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

Not applicable
Author

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')

Top10 FXn Forum.JPG

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

Top10 FXn Forum1.JPG

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.

sunny_talwar

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.

Not applicable
Author

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.

Top10 FXn Forum3.JPG

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')))