Creating Bucket in Qlikview Script

[yuvraj_33]

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.

[Anil_Babu_Samineni]

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'))))))

[yuvraj_33]

Dear Anil,

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

[Anil_Babu_Samineni]

Are you talking about this part?

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”;

Can you brief on this?

[yuvraj_33]

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

[Anil_Babu_Samineni]

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

[yuvraj_33]

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.

[Anil_Babu_Samineni]

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')

[Anil_Babu_Samineni]

My Bad,

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

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