Set Analysis

[krishna20]

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.

[#2]

Hi krishna20

Can you upload your qv File?

[PrashantSangle]

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

[hariprasadqv]

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.

[jagan]

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.

[krishna20]

Hi,

Yes I'm Having two dimensions two display this result. I need to show the Paid ratio.

Regards

Krishna

[krishna20]

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

[krishna20]

Hi Jagan,

I  tried using TOTAL but the result is same.No Change.

Regards

Krishna

[jagan]

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.

[krishna20]

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