topic Re: Logic in QlikView

Logic

panipat1990 — Thu, 18 Feb 2016 15:55:48 GMT

Hi Team,

I have a requirement...And I am Sharing A qvw........I have to Find. Frontend These Calucalation...

1. Net Realisation=Net Revenue-Selling Cost

2.Contribution=Net Realisation-Net Revenue-Selling Cost

3.Total=Overhead+Overhead+Cost Unit+Exceptional

4.Net Costag =Net Realisation/Total

How is it Possible..Please Help Me.....

Re: Logic

sunny_talwar — Thu, 18 Feb 2016 16:05:25 GMT

May be this:

Re: Logic

paul_vans267 — Thu, 18 Feb 2016 16:31:09 GMT

Hi there,

I moved your inline table into Excel and then used the Cross table wizard inside the File Wizard to change it into a flat table.

Then used the expressions that you suggested.

Hope this helps.

Re: Logic

panipat1990 — Thu, 18 Feb 2016 17:48:26 GMT

Hi Sir,

I want to keep Net Realisation below Selling Cost......and Contribution Below Material Cost Variable Sale......And Total Below Exception And Net Costag Below Less Cost...How is it possible

Re: Logic

sunny_talwar — Thu, 18 Feb 2016 17:54:14 GMT

Add a sort expression:

=Match(Pick(SNo, Particulers, 'Net Realisation', 'Contribution', 'Total', 'Net Costag'), 'Net Revenue', 'Selling Cost',

'Net Realisation', 'Material Cost', 'Variable Sale', 'Contribution', 'Overhead', 'Systemitic Unit', 'Cost Unit', 'Exceptional', 'Total',

'Gain', 'Less Cost', 'Net Costag')

Re: Logic

sathishkumar_go — Thu, 18 Feb 2016 17:58:50 GMT

Try This

Inline:
LOAD * INLINE [
Particulers, Budget, Actual
Net Revenue, 1000, 1500
Selling Cost, 2000, 2200
Material Cost, 3000, 2500
Variable Sale, 5000, 4000
Overhead, 4000, 5000
Systemitic Unit, 5000, 6000
Cost Unit, 6000, 7000
Exceptional, 7000, 6500
Gain, 8000, 9000
Less Cost, 9000, 10000
];

Inline:
LOAD * INLINE [
Dimension
Net Realisation
Contribution
Total
Net Costag
];

-Sathish

Re: Logic

sathishkumar_go — Thu, 18 Feb 2016 17:59:14 GMT

Re: Logic

sathishkumar_go — Thu, 18 Feb 2016 17:59:35 GMT

Expression:

=IF(Dimension = 'Net Realisation', Sum({<Particulers = {'Net Revenue'}>} Budget) - Sum({<Particulers = {'Selling Cost'}>} Budget),
IF(Dimension = 'Contribution', (Sum({<Particulers = {'Net Revenue'}>} Budget) - Sum({<Particulers = {'Selling Cost'}>} Budget)) - Sum({<Particulers = {'Net Revenue'}>} Budget) - Sum({<Particulers = {'Selling Cost'}>} Budget),
IF(Dimension = 'Total', (Sum({<Particulers = {'Overhead'}>} Budget) + Sum({<Particulers = {'Overhead'}>} Budget)) + Sum({<Particulers = {'Cost Unit'}>} Budget) + Sum({<Particulers = {'Exceptional'}>} Budget),
(Sum({<Particulers = {'Net Revenue'}>} Budget) - Sum({<Particulers = {'Selling Cost'}>} Budget)) / ((Sum({<Particulers = {'Overhead'}>} Budget) + Sum({<Particulers = {'Overhead'}>} Budget)) + Sum({<Particulers = {'Cost Unit'}>} Budget) + Sum({<Particulers = {'Exceptional'}>} Budget))
)))

Re: Logic

sathishkumar_go — Thu, 18 Feb 2016 17:59:59 GMT

Actual:

=IF(Dimension = 'Net Realisation', Sum({<Particulers = {'Net Revenue'}>} Actual) - Sum({<Particulers = {'Selling Cost'}>} Actual),
IF(Dimension = 'Contribution', (Sum({<Particulers = {'Net Revenue'}>} Actual) - Sum({<Particulers = {'Selling Cost'}>} Actual)) - Sum({<Particulers = {'Net Revenue'}>} Actual) - Sum({<Particulers = {'Selling Cost'}>} Actual),
IF(Dimension = 'Total', (Sum({<Particulers = {'Overhead'}>} Actual) + Sum({<Particulers = {'Overhead'}>} Actual)) + Sum({<Particulers = {'Cost Unit'}>} Actual) + Sum({<Particulers = {'Exceptional'}>} Actual),
(Sum({<Particulers = {'Net Revenue'}>} Actual) - Sum({<Particulers = {'Selling Cost'}>} Actual)) / ((Sum({<Particulers = {'Overhead'}>} Actual) + Sum({<Particulers = {'Overhead'}>} Actual)) + Sum({<Particulers = {'Cost Unit'}>} Actual) + Sum({<Particulers = {'Exceptional'}>} Actual))
)))

Re: Logic

sathishkumar_go — Thu, 18 Feb 2016 18:00:27 GMT

-Sathish

Re: Logic

panipat1990 — Thu, 18 Feb 2016 18:10:26 GMT

Thanks Sir...it is really helpful for me...but our requirement we need to get these fields from backend...so what is the possible way find out....please help me on this..

Re: Logic

sunny_talwar — Thu, 18 Feb 2016 19:00:45 GMT

Try this:

Inline:

LOAD * INLINE [

Particulers, Budget, Actual

Net Revenue, 1000, 1500

Selling Cost, 2000, 2200

Material Cost, 3000, 2500

Variable Sale, 5000, 4000

Overhead, 4000, 5000

Systemitic Unit, 5000, 6000

Cost Unit, 6000, 7000

Exceptional, 7000, 6500

Gain, 8000, 9000

Less Cost, 9000, 10000

];

Temp:

LOAD 1 as ID,

Budget as Rev_Budget,

Actual as Rev_Actual

Resident Inline

Where Particulers = 'Net Revenue';

Join (Temp)

LOAD 1 as ID,

Budget as Cost_Budget,

Actual as Cost_Actual

Resident Inline

Where Particulers = 'Selling Cost';

Concatenate(Inline)

LOAD 'Net Realisation' as Particulers,

Rev_Budget - Cost_Budget as Budget,

Rev_Actual - Cost_Actual as Actual

Resident Temp;

DROP Table Temp;

Temp:

LOAD 1 as ID,

Budget as Rev_Budget,

Actual as Rev_Actual

Resident Inline

Where Particulers = 'Net Revenue';

Join (Temp)

LOAD 1 as ID,

Budget as Cost_Budget,

Actual as Cost_Actual

Resident Inline

Where Particulers = 'Selling Cost';

Join (Temp)

LOAD 1 as ID,

Budget as Realization_Budget,

Actual as Realization_Actual

Resident Inline

Where Particulers = 'Net Realisation';

Concatenate(Inline)

LOAD 'Contribution' as Particulers,

Realization_Budget - Rev_Budget - Cost_Budget as Budget,

Realization_Actual - Rev_Actual - Cost_Actual as Actual

Resident Temp;

DROP Table Temp;

Temp:

LOAD 1 as ID,

Budget as OH_Budget,

Actual as OH_Actual

Resident Inline

Where Particulers = 'Overhead';

Join (Temp)

LOAD 1 as ID,

Budget as CU_Budget,

Actual as CU_Actual

Resident Inline

Where Particulers = 'Cost Unit';

Join (Temp)

LOAD 1 as ID,

Budget as Ex_Budget,

Actual as Ex_Actual

Resident Inline

Where Particulers = 'Exceptional';

Concatenate(Inline)

LOAD 'Total' as Particulers,

Ex_Budget + OH_Budget + CU_Budget as Budget,

Ex_Actual + OH_Actual + CU_Actual as Actual

Resident Temp;

DROP Table Temp;

Temp:

LOAD 1 as ID,

Budget as Realization_Budget,

Actual as Realization_Actual

Resident Inline

Where Particulers = 'Net Realisation';

Join (Temp)

LOAD 1 as ID,

Budget as Total_Budget,

Actual as Total_Actual

Resident Inline

Where Particulers = 'Exceptional';

Concatenate(Inline)

LOAD 'Net Costag' as Particulers,

Num(Realization_Budget/Total_Budget, '##.0%') as Budget,

Num(Realization_Actual/Total_Actual, '##.0%') as Actual

Resident Temp;

DROP Table Temp, SortOrder;

Re: Logic

panipat1990 — Thu, 18 Feb 2016 19:05:45 GMT

Thanks sir....it is helpful for me...