Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Buliano
Contributor II
Contributor II

Calculate subtotals and totals for date fields

Dear Community

I have a table where I have several entries per project, like the one in the example:

Buliano_0-1633447731105.png

I want to produce another table with QlikSense, summarizing the events per date, with subtotals per type of event, and totals, as below. For the empty fields, 0 or null are both ok. Selecting a box with a value should act as a filter, both in the subtotals and in the totals.

Buliano_1-1633447808262.png

For the dates on the left, a Master Calendar seems like the thing to do; if I use it, I can create one of the subtotals columns. However, as I have to link it with one of the date fields, I don't find a way to handle the rest of the columns.

Is this possible?

Thanks in advance

1 Solution

Accepted Solutions
JeanColeti
Contributor III
Contributor III

Hi,

You can try this.

[Table]:
CrossTable(Event,Date,1) LOAD *
FROM [lib://AttachedFiles/Teste.xlsx]
(ooxml, embedded labels, table is Planilha1);

You'll have just one date fiel

JeanColeti_0-1634060721830.png

 

View solution in original post

2 Replies
JeanColeti
Contributor III
Contributor III

Hi,

You can try this.

[Table]:
CrossTable(Event,Date,1) LOAD *
FROM [lib://AttachedFiles/Teste.xlsx]
(ooxml, embedded labels, table is Planilha1);

You'll have just one date fiel

JeanColeti_0-1634060721830.png

 

Buliano
Contributor II
Contributor II
Author

Hi,

Thanks for your reply.

Indeed, we could fix this by feeding QlikSense with the data as you suggest, then using a pivot table.

We didn't need to use  "CrossTable" function, though, as we could structure the data directly like that from our data source.

Kind regards