topic Re: Problem in Set analysis in QlikView

Problem in Set analysis

munna1317 — Fri, 20 Nov 2020 18:02:30 GMT

hi QVs,

Plz help me the requirement is in Excel plz help

regards harish

Re: Problem in Set analysis

Tue, 23 Sep 2014 10:50:14 GMT

what is the logic to get that column?

Re: Problem in Set analysis

avinashelite — Tue, 23 Sep 2014 10:55:56 GMT

Hi Harish,

Can you please provide the formula to calculate the output column

Re: Re: Problem in Set analysis

munna1317 — Tue, 23 Sep 2014 11:00:45 GMT

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

Re: Problem in Set analysis

munna1317 — Tue, 23 Sep 2014 11:01:52 GMT

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

thxs

harish

Re: Problem in Set analysis

JonnyPoole — Tue, 23 Sep 2014 11:19:21 GMT

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.

Re: Re: Problem in Set analysis

munna1317 — Tue, 23 Sep 2014 11:27:35 GMT

hi jonathan ,

Plz find the attachment ,

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

Thxs ,

Harish

Re: Problem in Set analysis

avinashelite — Tue, 23 Sep 2014 11:29:15 GMT

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.

Re: Problem in Set analysis

munna1317 — Tue, 23 Sep 2014 11:38:40 GMT

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

Re: Problem in Set analysis

munna1317 — Tue, 23 Sep 2014 11:48:51 GMT

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

Re: Problem in Set analysis

munna1317 — Tue, 23 Sep 2014 13:06:42 GMT

hi QV's,

Anyone With Answer ?, I can Close The Thread.

Regards ,

Harish

Re: Problem in Set analysis

avinashelite — Tue, 23 Sep 2014 13:08:45 GMT

Sorry , i was bit busy ...Working on it

Re: Problem in Set analysis

munna1317 — Tue, 23 Sep 2014 13:11:11 GMT

thanks Avinashelite,

me to working on it if any solution plz let me knw,

Regards,

Harish

Re: Problem in Set analysis

munna1317 — Wed, 24 Sep 2014 08:54:50 GMT

hi QV's ,

Any Solution

regards,

Harish

Re: Re: Problem in Set analysis

avinashelite — Wed, 24 Sep 2014 09:27:45 GMT

Hi Harish,

I found the issue, the flag is not getting set properly and second issue is....for numbers more than 5digits

eg 148466 you expecting as 14+1 but its reading as 1+1 so your getting the value 20000

I am half way done , but got busy with work....I am attaching the document please try to continue with this as base

Re: Re: Problem in Set analysis

munna1317 — Wed, 24 Sep 2014 09:48:40 GMT

hi avinash ,

Thxs for rply , what you have say is exactly correct ,

148466 you expecting as 14+1 but its reading as 1+1 so your getting the value 20000

I am also working on same expression ,

thxs once again ,

Regards ,

Harish

Re: Re: Re: Problem in Set analysis

munna1317 — Thu, 25 Sep 2014 09:08:29 GMT

hi Avinashelite,

i have got the result but the small problem , can you plz find out this i have gave expression:

If(Name=Previous(Name) and Div(CumulativeSum,50000)<Div(Previous(CumulativeSum),50000), Ceil(CumulativeSum/50000)*50000, Mod(CumulativeSum,50000)) as NewCumulativeSum

here iam getting error is

Name	Date	Year	Sales	CumulativeSum	NewCumulativeSum	Commission
Harish	12/08/2014	2014	7410	50594	594	0.01

here only it is rounding can plz help in this

Re: Re: Re: Re: Problem in Set analysis

avinashelite — Thu, 25 Sep 2014 14:02:19 GMT

Hi Harish,

Check this solution:

Re: Re: Re: Re: Problem in Set analysis

avinashelite — Thu, 25 Sep 2014 14:06:57 GMT

Hi Harish,

Please post the Exact Output what to want, so that i can cross check.

Re: Re: Re: Re: Re: Problem in Set analysis

munna1317 — Thu, 25 Sep 2014 14:36:29 GMT

hi avinash,

Plz Check Exact Output

Regards,

harish