Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Buliano
Contributor
Contributor

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

 

View solution in original post

Buliano
Contributor
Contributor
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