Sub Totali

[#1]

Hello Qlik Community

I'm a new Qlik user and as rookie I need your pleasant help.

As first question I would like to know if I can create sub total by field, and if yes how do that?

Second question is linked to the formula usage. In detail my need is to create a delta, as the picture enclosed, per each single month, in comparison with same month in previous year. Looking around I have found a formula which use the max() but create only one result while I need it for all the rows.

The formula is: SUM ({<[YEAR]= {"$(=MAX([YEAR]))"} REV) and including the -1 to find the previous year. I have also tried to change the formula without the max() but doesn't work

thanks in advance for your support

have a nice day

ciaooo

[buzzy996 · Accepted solution]

try tis first.,

[Anonymous]

could you post some sample data for your 2nd question?

For 1 st question, do what shiva suggested you...

[Anonymous]

To find out the max() of every month, you can try sth like this:

aggr(max(Amount), Month)         //you can use this as well as per your req {<[YEAR]= {"$(=MAX([YEAR]))"}

or

max(<Total Month> Amount

[Anonymous]

Hi Stefano,

you can create subtotal using Pivot table. Have a look into  Pivot table chart properties --> presentation layer --> Show partial sums.  You can enable the partial sum for any level of dimensions.

Regarding the formula, have a look into the below one:

sum(if (InMonth ([Order Date], SetDateYear (Today(), max (total Year ([Order Date]), 1)), 2), REV))

[simospa]

Ciao Stefano,

welcome in the community.

As first question I would like to know if I can create sub total by field, and if yes how do that?

Yes, you can easily insert sub totals in pivot tables in Chart Properties --> Presentation tab --> select the field for which you wish to see subtotal and check flag Show Partial Sum.

S.

[#7]

Hi Shiva

thanks your idea is easier and I have changed my first idea to have a normal table into pivot to get this advantage

[buzzy996]

np..hth.

[#9]

as clarification for the 2 question in the original post formula I have translated the field ANNO iso YEAR from the picture enclosed.

While what I would to achieve in the second case is the incremental REV, year over year. I cannot use the above/below formulas because I have different values in the 3rd column and are mixed. I need some formula which read the ANNO, MESE and 3° column in each row and take the REV referred to ANNO-1

thanks a lot for your help