topic Re: Export to excel total not matching with Qlik Straight table total in App Development

Export to excel total not matching with Qlik Straight table total

prashantsanchet — Tue, 06 Mar 2018 09:25:29 GMT

Hi team

I am having Straight Table as Chart

I am having a following as Dimention : Customer

measure:

if (vPeriod = 'YTD',

if (sum([Cost Price]) <> 0 ,

if (Sum([SP]) <> 0, num(sum([YTDSales]) - Sum([YTDFxSales]), '#,##0'), num(sum([YTDSales]), '#,##0')), 0),

)

Total in the Staright table is showing correct result.

but when i am trying to export this data and try to compare the total in excel and Total in Table is not matching .

What could b the reason .

Please provide your valuable comment as early as possible.

Re: Export to excel total not matching with Qlik Straight table total

Chanty4u — Tue, 06 Mar 2018 09:32:39 GMT

try

aggr(measure),Dim1,dim2)

Re: Export to excel total not matching with Qlik Straight table total

YoussefBelloum — Tue, 06 Mar 2018 09:35:37 GMT

Hi,

what is the total type you chose ?

Try this:

if (vPeriod = 'YTD' and sum([Cost Price]) <> 0 and Sum([SP]) <> 0, num(sum([YTDSales]) - Sum([YTDFxSales]), '#,##0'), num(sum([YTDSales]), '#,##0'), 0),

Re: Export to excel total not matching with Qlik Straight table total

prashantsanchet — Tue, 06 Mar 2018 09:36:52 GMT

Hi ,

This is not working. Does while exporting data to excel number format will have any issue ?

Re: Export to excel total not matching with Qlik Straight table total

Chanty4u — Tue, 06 Mar 2018 09:37:54 GMT

how much do you see the difference?

Re: Export to excel total not matching with Qlik Straight table total

Chanty4u — Tue, 06 Mar 2018 09:39:19 GMT

can you add the rowno() as one more column and see which is missing with the original count?

Re: Export to excel total not matching with Qlik Straight table total

prashantsanchet — Tue, 06 Mar 2018 10:11:55 GMT

Hi ,

I added but the still result is off. I am hiding zero values and showing null value in dimension but still same result.

Re: Export to excel total not matching with Qlik Straight table total

Chanty4u — Tue, 06 Mar 2018 10:13:49 GMT

what is the exact number? and what your getting?

Re: Export to excel total not matching with Qlik Straight table total

prashantsanchet — Tue, 06 Mar 2018 10:49:01 GMT

In Qlik i am getting let say 6927833.25 while in excel its showing 6702995.61.

Re: Export to excel total not matching with Qlik Straight table total

Chanty4u — Tue, 06 Mar 2018 12:04:00 GMT

give me two columns result in excel and validate row by row .

then you come to know which row/column having the issue. [it may be format from excel or qlik]

Re: Export to excel total not matching with Qlik Straight table total

prashantsanchet — Tue, 06 Mar 2018 13:19:41 GMT

Hi ,

The problem i am seeing that could be due to "Total Mode ".

The total mode used was Auto and for testing when i make total mode as Sum the value are matching with excel .

But the problem is How to set Total mode for KPI and Subtitle in Bar Chart having same expression.

I think default it will be like "AUTO total mode".

Because the aim is Subtitle should have same value as exported data .

Please provide your comment.

Re: Export to excel total not matching with Qlik Straight table total

prashantsanchet — Tue, 06 Mar 2018 13:21:47 GMT

Hi ,

The problem i am seeing that could be due to "Total Mode ".

The total mode used was Auto and for testing when i make total mode as Sum .

The value are matching with excel .

But the problem is How to set Total mode for KPI and Subtitle in Bar Chart using same expression.

I think default it will be like "AUTO total mode".

Because the aim is Subtitle should have same value as exported data

Re: Export to excel total not matching with Qlik Straight table total

YoussefBelloum — Tue, 06 Mar 2018 13:31:17 GMT

I'm not sure I can follow,

you should always set a total type for each measure, "sum of line", "total of expression".. you should see what is the value you're looking for and what is the correct combination depending on the dimensions you have.

Re: Export to excel total not matching with Qlik Straight table total

prashantsanchet — Tue, 06 Mar 2018 13:37:09 GMT

How to set Total type for measure which we are using in subtitle of chart ?

Re: Export to excel total not matching with Qlik Straight table total

YoussefBelloum — Tue, 06 Mar 2018 13:49:39 GMT

Can you a screen shot of what you mean by this ? by subtitle of chart ?

Re: Export to excel total not matching with Qlik Straight table total

big_dreams — Tue, 06 Mar 2018 14:14:33 GMT

This is might be data model issue. As chanty suggested aggr work for you.

but for that you have to apply aggr() on every expression written inside if()

num(if (vPeriod = 'YTD',

if (sum(aggr(sum([Cost Price]),Customer)) <> 0 ,

if (Sum(aggr(Sum([SP]),Customer)) <> 0, Sum(aggr(sum([YTDSales]),Customer)) - sum(aggr(Sum([YTDFxSales]),Customer)), sum(aggr(sum([YTDSales]),Customer)),0),

),'#,##0')

Note : correct if there is any bracket or syntax error.

Regards,

Re: Export to excel total not matching with Qlik Straight table total

prashantsanchet — Tue, 06 Mar 2018 14:35:42 GMT

Hi Team ,

KPI : Total Sales variance Value : -0.12

In the Bar chart Subtitle Value is - -0.1199 (-0.12)

But expected value in Subtitle is : 1682177.64 which is (after exporting data to excel)

How to achive this ?

Re: Export to excel total not matching with Qlik Straight table total

prashantsanchet — Tue, 06 Mar 2018 15:07:18 GMT

Hi Team ,

KPI : Total Sales variance Value : -0.12

In the Bar chart Subtitle Value is - -0.1199 (-0.12)

But expected value in Subtitle is : 1682177.64 which is (after exporting data to excel)

How to achive this ?

Re: Export to excel total not matching with Qlik Straight table total

YoussefBelloum — Tue, 06 Mar 2018 15:09:19 GMT

What do you have on the title expression ?

Under ASPECT => GENERAL

Re: Export to excel total not matching with Qlik Straight table total

prashantsanchet — Tue, 06 Mar 2018 15:37:52 GMT

I have following expression in subtitle

=if((sum([YTD Sales Amount])-sum([LY YTD Sales Amount]))/sum([LY YTD Sales Amount])>-0.45,

(sum([YTD Sales Amount])-sum([LY YTD Sales Amount]))/sum([LY YTD Sales Amount]),sum([YTD Sales Amount]) )