topic Re: problem with subtotal in QlikView

problem with subtotal

Masi_Sahargahi — Sun, 04 Oct 2015 12:32:06 GMT

Hi guys,I'm new to Qlikview and i would be very appreciated if you help me out of this:

I have a pivot table that have some dimensions,I want to enable subtotal in my pivot table,Everything was fine until I added an expression that uses set analysis to filter data(between dates that user enters using input boxes).In this case subtotal disappears and I don't know how to fix it.

the expression is like that :

=if((OrderDate>=StartDate and OrderDate<=EndDate)or ([BillDate>=StartDate and [BillDate]<=EndDate),num(Sum({<oTypeNo={110}>}F_sraQuantitySecondUnit)),0)

Thanks a lot

Re: problem with subtotal

Gysbert_Wassenaar — Sun, 04 Oct 2015 15:02:25 GMT

The condition of your if statement cannot be calculated at the subtotal level. OrderDate >=StartDate... which orderdate and which startdate should be used at the subtotal level? Qlikview doesn't know which of the possible values you want it to use.

Re: problem with subtotal

Masi_Sahargahi — Sun, 04 Oct 2015 16:08:02 GMT

thanks for reply

I want to have subtotal of the records that meet this condition(OrderDate >=StartDate) , How can I get that?

ant other solution?

Re: problem with subtotal

Gysbert_Wassenaar — Sun, 04 Oct 2015 17:24:22 GMT

If you can use a straight table instead of a pivot table the solution is very simply: set the Total mode of the expression to Sum of rows. If you must use a pivot table you will have to use the aggr function to add an extra level of aggregation for the sums of the subtotals. Something like sum(aggr( ...if( ..etc...) , DimA,DimB,DimC) where DimA, DimB and DimC are the dimensions of your pivot table.

Re: problem with subtotal

Masi_Sahargahi — Sun, 04 Oct 2015 18:33:46 GMT

sorry for asking such a basic question, but with using straight table what would I loose(rather than using pivot table)?

thanks again

Re: problem with subtotal

Gysbert_Wassenaar — Mon, 05 Oct 2015 12:45:07 GMT

You lose the ability to pivot a dimension to columns, the option to collaps/expand dimensions, the option to show subtotals at the dimension levels.

Re: problem with subtotal

Masi_Sahargahi — Mon, 05 Oct 2015 17:36:13 GMT

Actually I solved it with deleting the condition and using set analysis instead.

thanks a lot