Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help with displaying totals on same line

Hi, I am facing a problem displaying summary data for two or more data sets on one line

An abridged form of the data is

Data-Set   Attribute      Value-1      Value-2

Plan           A1               100               50

Plan           A1               200               80

Plan           A2                60                 30

Actual        A1                20                 10

Actual        A2                30                 20

Actual        A3                10                   5

Bud-1        A1                 50                 25

Bud-2        A3                 80                 40

   

I want to display a table chart selecting two Data-Sets - eg Plan and Actual  and displaying

Attribute            Total-Value-1(Data-Set=Plan)    Total-Value-1(Data-Set=Actual)   Variance(Plan/Actual)

A1                            200                                          20                                                      -180

A2                              60                                          30                                                        -30

If I have a chart with dimensions Data-Set(coilumn hidden) and Attribute

and expression of 

IF([Data-Set]='Plan',sum(Value-1),0)

IF([Data-Set]='Actual',sum(Value-1),0) etc

I get two lines  for each attribute

Attribute            Total-Value-1(Data-Set=Plan)    Total-Value-1(Data-Set=Actual)   Variance(Plan/Actual)

A1                            200                                          0                                                          -200

A1                               0                                         20                                                             20

A2                              60                                         0                                                             -60

A2                               0                                         30                                                             30

How can I get a single line please?

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Use Attribute as dimension and three expressions:

  • sum({<[Data-Set]={'Plan'}>} [Value-1])
  • sum({<[Data-Set]={'Actual'}>} [Value-1])
  • sum({<[Data-Set]={'Plan'}>} [Value-1])/sum({<[Data-Set]={'Actual'}>} [Value-1])

talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Gysbert_Wassenaar

Use Attribute as dimension and three expressions:

  • sum({<[Data-Set]={'Plan'}>} [Value-1])
  • sum({<[Data-Set]={'Actual'}>} [Value-1])
  • sum({<[Data-Set]={'Plan'}>} [Value-1])/sum({<[Data-Set]={'Actual'}>} [Value-1])

talk is cheap, supply exceeds demand
sasiparupudi1
Master III
Master III

Thread 169724.png

Not applicable
Author

Tks very much - works a treat!

Not applicable
Author

Tks Sasidhar - that works well - sorry my level is not even close to you guys