topic Working with ranges on Income Statement in QlikView

Working with ranges on Income Statement

Wed, 29 Jul 2015 16:46:14 GMT

Hi all,

Working with ranges like accounts seem to be a bit tricky. Anyway, I'm trying to build Income Statement with a lot of account ranges and dimensions. My data model is a bit complex based on Fact and Dimensions but I am simplifying the model in this table:

AccountNO	Amount	Dimension	Year
1	100	1	2014
2	100	2	2014
3	100	3	2014
4	100	1	2014
5	100	2	2014
6	100	3	2014
7	100	1	2014
8	100	2	2014
9	100	3	2014
10	100	1	2014

So let's say I want to calculate the following and show them in one straight table:

1. Revenue: Range AccountNo (1 to 😎 AND Dimension between (2 to 3)

2. Expenses: Range AccountNo (5 to 10) AND Dimension between (1 and 2)

3. Gross Profit = Revenue - Expenses

4. Gross Margin = Gross Profit / Revenue

So the output should be something like this:

Title	Amount	Year
Revenue	400	2014
Expenses	300	2014
Gross Profit	100	2014
Gross Margin	0.25	2014

I'm not looking for a complete solution, just any tip/advice on what could the best way to load and handle the above logic would be appreciated.

Thanks.

Re: Working with ranges on Income Statement

sunny_talwar — Wed, 29 Jul 2015 16:55:59 GMT

Seems like I get a slightly different number for Revenue and Expense based on the logic you gave and the conditions:

UPDATE IMAGE

But check it out and see if this helps.

Re: Working with ranges on Income Statement

Wed, 29 Jul 2015 17:23:20 GMT

Thanks Sunny. Just because the real scenario is a bit more complex and involves a wide set of records which will impact the performance in case the logic was in the set analysis. So could that processing handled via scripts during the ETL?

Re: Working with ranges on Income Statement

Kushal_Chawda — Wed, 29 Jul 2015 17:36:37 GMT

Take a look at the another way

Re: Working with ranges on Income Statement

Wed, 29 Jul 2015 17:44:32 GMT

It's another good solution in case I want handle the logic at the user's tier (set analysis) Thanks Kush. However as I mentioned above I'm looking to handle the logic in the loading script.

Thanks again.

Re: Working with ranges on Income Statement

sunny_talwar — Wed, 29 Jul 2015 17:53:18 GMT

This?

Script:

Table:

LOAD AccountNO,

Amount,

Dimension,

Year

FROM

[https://community.qlik.com/thread/174540]

(html, codepage is 1252, embedded labels, table is @1);

Temp:

LOAD Sum(Amount) as Amount,

'Revenue' as Title,

Year

Resident Table

Where AccountNO >= 1 and AccountNO <= 8 and Dimension >=2 and Dimension <=3

Group By Year;

Concatenate(Temp)

LOAD Sum(Amount) as Amount,

'Expenses' as Title,

Year

Resident Table

Where AccountNO >= 5 and AccountNO <= 10 and Dimension >=1 and Dimension <=2

Group By Year;

Concatenate(Temp)

LOAD Sum(If(AccountNO >= 1 and AccountNO <= 8 and Dimension >=2 and Dimension <=3, Amount))-

Sum(If(AccountNO >= 5 and AccountNO <= 10 and Dimension >=1 and Dimension <=2, Amount)) as Amount,

'Gross Profit' as Title,

Year

Resident Table

Where AccountNO >= 1 and AccountNO <= 10 and Dimension >=1 and Dimension <=3

Group By Year;

Concatenate(Temp)

LOAD (Sum(If(AccountNO >= 1 and AccountNO <= 8 and Dimension >=2 and Dimension <=3, Amount))-

Sum(If(AccountNO >= 5 and AccountNO <= 10 and Dimension >=1 and Dimension <=2, Amount)))/

Sum(If(AccountNO >= 1 and AccountNO <= 8 and Dimension >=2 and Dimension <=3, Amount)) as Amount,

'Gross Margin' as Title,

Year

Resident Table

Where AccountNO >= 1 and AccountNO <= 10 and Dimension >=1 and Dimension <=3

Group By Year;

DROP Table Table;

Re: Working with ranges on Income Statement

Kushal_Chawda — Wed, 29 Jul 2015 18:11:01 GMT

ok so here is the script

Table:

LOAD * Inline [

AccountNO, Amount, Dimension, Year

1, 100, 1, 2014

2, 100, 2, 2014

3, 100, 3, 2014

4, 100, 1, 2014

5, 100, 2, 2014

6, 100, 3, 2014

7, 100, 1, 2014

8, 100, 2, 2014

9, 100, 3, 2014

10, 100, 1, 2014 ];

Data:

LOAD sum(if((AccountNO>=1 and AccountNO<=8) and (Dimension>=2 and Dimension<=3) ,Amount)) as AMT,

Year ,

'Revenue' as Title

Resident Table

Group By Year;

Concatenate

LOAD sum(if((AccountNO>=5 and AccountNO<=10) and (Dimension>=1 and Dimension<=2) ,Amount)) as AMT,

Year ,

'Expenses' as Title

Resident Table

Group By Year;

Concatenate

LOAD sum(if(Title='Revenue', AMT)) - sum(if(Title='Expenses', AMT)) as AMT,

Year,

'Gross Profit' as Title

Resident Data

Group by Year;

Concatenate

LOAD sum(if(Title='Gross Profit', AMT))/sum(if(Title='Revenue', AMT)) as AMT,

Year,

'Gross Margine' as Title

Resident Data

Group by Year;

Re: Working with ranges on Income Statement

Thu, 30 Jul 2015 18:19:13 GMT

Thank you guys, you are the best!

Re: Working with ranges on Income Statement

Kushal_Chawda — Thu, 30 Jul 2015 18:41:03 GMT

LouieERI we are always happy to help