topic Re: Creating Bucket in Qlikview Script in QlikView

Creating Bucket in Qlikview Script

yuvraj_33 — Mon, 11 Sep 2017 12:06:41 GMT

Dear All,

Currently stuck in a scenario where I have to implement following bucket:

If (ABS(Budgeted – Actual))<0.25 AND Actual <0.25, THEN

“Miscellaneous”;

If Budgeted>0 AND Actual =0, THEN “Exhibit Not Yet Started”;

If Budgeted =0 AND Actual>0, THEN search for the Budgeted Value in subsequent quarters of that particular year, if the Budgeted Value is found in other quarters Then “Preponed”, Else “Unbudgeted”;

If Budgeted =0 AND Actual=0, THEN “No RM Cost –OH Allocated”;

If Budgeted<Actual, THEN “Budget Exceeded”;

If Budgeted>Actual, THEN “Exhibit Batch Ongoing/Positive Variance”;

Else ERROR.

Actual Values are coming from SAP at Month Year Level whereas Budget from Excel file at Quarter level.

Analysis & Calculations are to be shown at Project, Year & Quarter level.

Please find attached sample data.

Re: Creating Bucket in Qlikview Script

Anil_Babu_Samineni — Mon, 11 Sep 2017 12:13:23 GMT

May be this?

If(Fabs(Budgeted – Actual)<0.25 and Actual <0.25, 'Miscellaneous',

If(Budgeted>0 and Actual =0, 'Exhibit Not Yet Started;

If(Budget = 0 and Actual >0, If(Year = 2017, Budgeted, 'Preponed'), 'Unbudgeted'),

If(Budgeted = 0 and Actual = 0, 'No RM Cost –OH Allocated',

If(Budgeted < Actual, 'Budget Exceeded',

If(Budgeted > Actual, 'Exhibit Batch Ongoing/Positive Variance', 'ERROR'))))))

Re: Creating Bucket in Qlikview Script

yuvraj_33 — Tue, 12 Sep 2017 07:29:52 GMT

Dear Anil,

The above mentioned solution didn't worked as it calculates at line item level which is Quarter level in this case.

Re: Creating Bucket in Qlikview Script

Anil_Babu_Samineni — Tue, 12 Sep 2017 07:34:30 GMT

Are you talking about this part?

Can you brief on this?

Re: Creating Bucket in Qlikview Script

yuvraj_33 — Tue, 12 Sep 2017 08:03:03 GMT

Yes, that part. Have attached sample data & few cases. Pls chk.

Re: Creating Bucket in Qlikview Script

Anil_Babu_Samineni — Tue, 12 Sep 2017 08:05:39 GMT

Yes, Excel is there but i am expecting how this content need to be as calculated. I am not sure i understand over here

Re: Creating Bucket in Qlikview Script

yuvraj_33 — Tue, 12 Sep 2017 08:41:13 GMT

Please find below example:

Project	Year	Quarter	Actual	Budget	Bucket Values (For Understanding)
P1	2017	Q1	10	0	Preponed
P1	2017	Q2	0	0	No Cost Allocated
P1	2017	Q3	0	20	Exhibit Not Yet started
P1	2017	Q4	0	0	No Cost Allocated
P2	2017	Q1	0	0	No Cost Allocated
P2	2017	Q2	10	0	Unbudgeted
P2	2017	Q3	0	0	No Cost Allocated
P2	2017	Q4	0	0	No Cost Allocated

In above scenario, for Project P1 Actuals are defined in Q1 whereas Budget is defined in Q3. In this case, if Budget is not available in corresponding quarter, it should check for Budget value in subsequent quarter. If Budget is found in subsequent quarters As Q3 in this case, the bucket value should be "Preponed" that should reflecting across Q1 where Actual is defined.

Similarly for project P2, Actuals is defined in Q2 whereas there is no Budget Value in corresponding quarter & subsequent quarters. In this case "Unbudgeted" should reflect across Q2 Quarter of Project P2.

The case "Exhibit not yet started" is because there is Budget defined for P1 in Q3 but no Actual is defined for Q3.

The case "No cost allocated" is because there are no Actual & Budget values defined for respective Quarters..

Hope you would understand now.

Re: Creating Bucket in Qlikview Script

Anil_Babu_Samineni — Tue, 12 Sep 2017 09:04:10 GMT

some thing like below?

If((Match(Quarter = 'Q1') and Actual >0 and Budget >0) or

(Match(Quarter = 'Q3') and Actual >0 and Budget >0) , 'Preponed')

Re: Creating Bucket in Qlikview Script

Anil_Babu_Samineni — Wed, 13 Sep 2017 03:32:57 GMT

My Bad,

If((Match(Quarter, 'Q1') and Actual >0 and Budget >0) or

(Match(Quarter, 'Q3') and Actual >0 and Budget >0) , 'Preponed')