Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

Financial Statements, Problem using SET Analysis

I need to create a financial statement that includes a percent of NET SALES column for three time frames, (ACTUAL, BUDGET, PREVIOUS MONTH) . I am using a chart table . I need to divide the line entries by their respective NET SALES amount. (ACTUAL LINE items divided by ACTUAL net sales total, BUDGET LINE items divided by BUDGET net sales total, , PREVIOUS MONTH LINE items divided by PREVIOUS MONTH net sales total)

I have tried to use Set Analysis to do this by creating three variables but I only get the first variable value for all three columns in the TABLE

vNETSACT=SUM({<(TYPE={ACT}),LINEID={NET SALES}>}AMOUNT)

vNETSBUD=SUM({<(TYPE={BUD}), LINEID={NET SALES}>}AMOUNT)

vNETSPRE=SUM({<(TYPE={PREV}), LINEID={NET SALES}>}AMOUNT)

And then create an expressions with

Columns that have these calulations

The spreadsheet I need to replicate looks like this:

SUM(AMOUNT)

SUM(AMOUNT)/vNETSA



SUM(AMOUNT)

SUM(AMOUNT)/vNETSBUD



SUM(AMOUNT)

SUM(AMOUNT)/vNETSPRE



ACTUAL

ACTUAL

BUDGET

BUDGET

PREVIOUS

PREVIOUS

ITEM

Amount

% NS

Amount

% NS

Amount

% NS

Gross Sales of Goods

61,781

114.8%

52,974

105.9%

55,488

112.9%

Sales Discounts

7,970

14.8%

3,159

6.3%

6,842

13.9%

Net Sales

53,811

100.0%

50,000

100.0%

49,145

100.0%

Administration Cost

825

1.5%

1,081

2.2%

870

1.8%

Income/Expenses I

-138

-0.3%

68

0.1%

-79

-0.2%

Indirect Cost of Distribution

1,439

2.7%

959

1.9%

844

1.7%

Market and Sales - Organization

2,632

4.9%

2,250

4.5%

2,762

5.6%

Research & Development I

82

0.2%

73

0.1%

82

0.2%

Extraordinary Items

-138

-0.3%

68

0.1%

-79

-0.2%