Finding Sum on Rows and Column based on condition

[kattesang]

I have a data as below.

I have to find the total as below.

Please let me know anyone, is this possible in qlik sense? If possible, how to do this?

Thanks.

[nisha_rai]

Hi ,

you have to convert your columns into rows , with the help of cross table.

Have a look at the below screen shot, May it helps

CrossTable (Status,Data,2)
Load * Inline [
Title 1, Title 2, New, Active, Closed
A1, A11,1,1,0
A1,A12,0,1,2
A1,A13,2,0,2
B1,B11,1,5,4
B1,B12,2,4,0
B1,B13,3,2,1
];

[kattesang]

As I am new to Qlik, i don't have any idea about Cross Table. And the records are dynamic. But in your solution, you have hard coded the record data.

I able to achieve the row wise total by assigning the below expression in total column.

=if ([Title2] = '' and Sum(New + Active + Closed) > 0, Sum(New + Active + Closed), '')

So, in similar way is there any way to achieve column wise total?

Thanks.

[nisha_rai]

Hi,

Code is not hard coded,  you have to do the scripting like the below code, Open the Script editor and above your load just put the below code

CrossTable (Status,Data,2)

Status is like the name of new field which gives you Aactive, Cancel etc and data is the value of each status,

It's more similar like in excel you covert the columns into rows 

if you use that cross table in script it's make your front end code much easier

[nisha_rai]

f you can prove me sample data i can post , how you can use cross table function.

[kattesang]

Thanks Nisha. As you asked, I have enclosed a sample data in excel sheet format. Please let me know any other information needed to help me.

[nisha_rai]

Hi,

Test:

Load * ,

if(len(trim(Title1))=0, peek(Title11), Title1)As Title11;
LOAD
Title1,
Title2,
New,
Active,
Closed
FROM [lib://AttachedFiles/Book1.xlsx]
(ooxml, embedded labels, table is Sheet1)
;

Test1:
CrossTable (Status,Data,2)
Load Title11 as Title1,Title2,
New,
Active,
Closed
Resident Test
where Title2<> null();
Drop Table Test;

Exit Script;

May this will resolve your issue,

After that in Fron end  Row Dimension= Title1 and Title 2

Column Dimension = Status

Expression= Sum(Data)

Under Presentation = Unchecked the Indent row to enable  show total function in each dimensions.

[Andy_ASDF]

Hi, just want to propose an alternative way.

Create a new expression called "Total", the formula would be:

"Column(1)+Column(2)+Column(3)"

provided that "New", "Active" and "Closed" are your expressions in your table.

Example:

=if ([Title2] = '' , Column(1)+Column(2)+Column(3), 0)

or

=if (isnull([Title2]) = 0 , Column(1)+Column(2)+Column(3), 0)

[kattesang]

Hi Nisha,

Load * ,

if(len(trim(Title1))=0, peek(Title11), Title1)As Title11;

Please explain the above statement. In my excel sheet there is no column with the name Title11.

And when I remove those statement,  it is loading only Active, New, Status and Data. It is not loading Title1 and Title2. Please clarify me.

Thanks

[nisha_rai]

Hi,

Yes, Title11 is not in your excel file, it is created by my self to make it A1 enable for all A related title2.

example:

to make you data like the belo table i created new field which is Title11, after that i rename it Title1

Title 1, Title 2
A1, A11
A1,A12
A1,A13
B1,B11
B1,B12
B1,B13