Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Friends,
I'm having pivot table showing data comparison for the three years. In this chart i need to add an expression which means to calculate
(Total Sales per the year / Total Sales) * 100. I wrote the expression as
=(sum({<SETL_DTYear={$(vMaxClmIntmYear)}, EST_TYPE={1}>}AMT_LC_1)
-sum({<SETL_DTYear={$(vMaxClmIntmYear)},EST_TYPE={3}>}AMT_LC_1))
-(sum({<SETL_DTYear={$(vMaxClmIntmYear)},EST_TYPE={2}>}AMT_LC_1)
-(sum({<SETL_DTYear={$(vMaxClmIntmYear)},EST_TYPE={4}>}AMT_LC_1)))
/
((sum({<EST_TYPE={1}>}AMT_LC_1)
-sum({<EST_TYPE={3}>}AMT_LC_1))
-(sum({<EST_TYPE={2}>}AMT_LC_1)
-(sum({<EST_TYPE={4}>}AMT_LC_1))))
I already said that i have included year filter in the pivot table to show year wise data. Now I want include this expression this pivot table.Whether the above expression is fine or i need to change to change anything in this expression. I'm in little confusion. Please find the snapshot for clear understanding.Kindly, suggest me how to overcome from this issue.
Hi krishna20
Can you upload your qv File?
Hi,
You can optimize your expression
Try like,
=(
sum({<SETL_DTYear={$(vMaxClmIntmYear)}, EST_TYPE={1}>}AMT_LC_1)
-
sum({<SETL_DTYear={$(vMaxClmIntmYear)},EST_TYPE={'3','2','4'}>}AMT_LC_1))
)
/
(
sum({<EST_TYPE={1}>}AMT_LC_1)
-sum({<EST_TYPE={'2','3','4'}>}AMT_LC_1))
)
Regards
Hi krishna,
As per my view, use a variable for caliculating the total sales and use the variable when you are deviding the year sales. then It would help you.
Hi,
Use TOTAL to ignore the dimension
=(sum({<SETL_DTYear={$(vMaxClmIntmYear)}, EST_TYPE={1}>}AMT_LC_1)
-sum({<SETL_DTYear={$(vMaxClmIntmYear)},EST_TYPE={3}>}AMT_LC_1))
-(sum({<SETL_DTYear={$(vMaxClmIntmYear)},EST_TYPE={2}>}AMT_LC_1)
-(sum({<SETL_DTYear={$(vMaxClmIntmYear)},EST_TYPE={4}>}AMT_LC_1)))
/
((sum({<EST_TYPE={1}>} TOTAL AMT_LC_1)
-sum({<EST_TYPE={3}>} TOTAL AMT_LC_1))
-(sum({<EST_TYPE={2}>} TOTAL AMT_LC_1)
-(sum({<EST_TYPE={4}>} TOTAL AMT_LC_1))))
If you have any other dimensions other than year then use below expression
=(sum({<SETL_DTYear={$(vMaxClmIntmYear)}, EST_TYPE={1}>}AMT_LC_1)
-sum({<SETL_DTYear={$(vMaxClmIntmYear)},EST_TYPE={3}>}AMT_LC_1))
-(sum({<SETL_DTYear={$(vMaxClmIntmYear)},EST_TYPE={2}>}AMT_LC_1)
-(sum({<SETL_DTYear={$(vMaxClmIntmYear)},EST_TYPE={4}>}AMT_LC_1)))
/
((sum({<EST_TYPE={1}>} TOTAL <SETL_DTYear> AMT_LC_1)
-sum({<EST_TYPE={3}>} <SETL_DTYear> TOTAL AMT_LC_1))
-(sum({<EST_TYPE={2}>} <SETL_DTYear> TOTAL AMT_LC_1)
-(sum({<EST_TYPE={4}>} <SETL_DTYear> TOTAL AMT_LC_1))))
Hope this helps you.
Regards,
Jagan.
Hi,
Yes I'm Having two dimensions two display this result. I need to show the Paid ratio.
Regards
Krishna
Hi all,
My Question is that there will be any data issue if we calculate for the particular year and having year dimension in the same chart?
Regards
Krishna
Hi Jagan,
I tried using TOTAL but the result is same.No Change.
Regards
Krishna
HI,
TOTAL will ignore the dimensions. Is the Year name used in TOTAL and dimension are same? If possible attach sample file, then it would be easier to give solution.
Regards,
Jagan.
Hi Jagan,
Yes the Year field is the same. sorry i can't upload the sample file. File is too large to upload.
Regards
Krishna