Problem in Set analysis

[munna1317]

hi QVs,

Plz help me the requirement is in Excel plz help

regards harish

[#2]

what is the logic to get that column?

[avinashelite]

Hi Harish,

Can you please provide the formula to calculate the output column

[munna1317]

hi Avinash ,

No formula is there the output table must be develop , in QlikView i have one idea ,

but at 200000 its getting minus value , plz find the attactment

[munna1317]

i need to implement logic if logic is there it is easy to get the result

thxs

harish

[JonnyPoole]

I agree  with Avinash R and QV Learner... what does the last column mean ?  If there is no formula for it in excel please describe what it actually is.

[munna1317]

hi jonathan ,

Plz find the attachment ,

and plz check the Cumulativesum3 field you will idea about requirement,

Thxs ,

Harish

[avinashelite]

Hi Harish,

Can you please explain the logic that your following...I saw your qvw, peek(cummsum) for few values its taking null values that's why its showing minus values.

If you explain the logic, it will be helpful for us to give the solution.

[munna1317]

hi ,

plz check this :

if(ChangeFlag2=1,left(CumulativeSum,1)+1 & repeat('0', len(CumulativeSum)-1), peek(CumulativeSum2)) as CumulativeSum2

here i am taking the left most character of Cumulative add addng that one ,

but at step of

Name	Date	Year	Sales	Commission	CumulativeSum	CumulativeSum3
Harish	12/28/2014	2014	1516	0.02	148466	200000
Harish	12/29/2014	2014	5137	0.025	153603	-46397
Harish	12/30/2014	2014	6468	0.025	160071	-39929

here it should taking left(CumulativeSum,1) is 1 that is problem iam getting but it should take 14 +1 =15 the output will be correct :

Expected Out put

Name	Date	Year	Sales	Commission	CumulativeSum	CumulativeSum3
Harish	12/28/2014	2014	1516	0.02	148466	150000
Harish	12/29/2014	2014	5137	0.025	153603	3603
Harish	12/30/2014	2014	6468	0.025	160071	10071

like this it will come any idea about this logic

[munna1317]

the blind logic is for every end of different commission the Cumulative sum must be Round with Below cumulative sum and the it shoould starts form

if(ChangeFlag2=1,  CumulativeSum2, CumulativeSum - alt(CumulativeSum2,0)) as Cumulativesum3

like this some logic i got in mind when seeing that excel

thxs ,

Harish