topic Re: Sub Totali in QlikView

Sub Totali

Wed, 25 Nov 2015 10:38:12 GMT

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

Re: Sub Totali

buzzy996 — Thu, 26 Nov 2015 09:53:24 GMT

try tis first.,

Re: Sub Totali

Anonymous — Thu, 26 Nov 2015 10:09:30 GMT

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

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

Re: Sub Totali

Anonymous — Thu, 26 Nov 2015 10:13:28 GMT

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]))"}

max(<Total Month> Amount

Re: Sub Totali

Anonymous — Thu, 26 Nov 2015 10:13:56 GMT

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))

Re: Sub Totali

simospa — Thu, 26 Nov 2015 10:16:03 GMT

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.

Re: Sub Totali

Thu, 26 Nov 2015 10:45:33 GMT

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

Re: Sub Totali

buzzy996 — Thu, 26 Nov 2015 10:51:42 GMT

np..hth.

Re: Sub Totali

Thu, 26 Nov 2015 11:18:18 GMT

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