topic Re: Set Analysis in QlikView

Set Analysis

krishna20 — Wed, 18 Mar 2015 06:12:12 GMT

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.

Re: Set Analysis

Wed, 18 Mar 2015 07:28:42 GMT

Hi krishna20

Can you upload your qv File?

Re: Set Analysis

PrashantSangle — Wed, 18 Mar 2015 07:34:42 GMT

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

Re: Set Analysis

hariprasadqv — Wed, 18 Mar 2015 07:35:50 GMT

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.

Re: Set Analysis

jagan — Wed, 18 Mar 2015 08:07:50 GMT

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.

Re: Set Analysis

krishna20 — Wed, 18 Mar 2015 08:56:51 GMT

Hi,

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

Regards

Krishna

Re: Set Analysis

krishna20 — Wed, 18 Mar 2015 09:07:44 GMT

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

Re: Set Analysis

krishna20 — Wed, 18 Mar 2015 09:25:30 GMT

Hi Jagan,

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

Regards

Krishna

Re: Set Analysis

jagan — Wed, 18 Mar 2015 10:08:46 GMT

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.

Re: Set Analysis

krishna20 — Wed, 18 Mar 2015 11:06:16 GMT

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