Export to excel total not matching with Qlik Straight table total

[prashantsanchet]

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.

[YoussefBelloum · Accepted solution]

Try this to replace the one above:

=Sum(aggr(

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

Customer))

[kaushiknsolanki]

Hi,

What is the chart expression "Calc"in your case?

Also which chart you are exporting? Table or bar chart?

Regards,

Kaushik Solanki

[prashantsanchet]

Chart Expression :

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

Chart Type : Bar Chart

[kaushiknsolanki]

Try this expression.

=Sum(Aggr(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])  ),Customer))

Regards,

Kaushik Solanki

[big_dreams]

if possible share your qvf.

Regards

[prashantsanchet]

Hi Team,

Thank you . This is working when i used a static dimension in Aggr. But  I have requirement where

i will have a dropdown selctor like (Dynamic Dimention Name)  like Customer, Region, Country and it will be stored in variable

vSelector : This will have static string 'Customer' ,'Region,' 'Country'

I have Variable storing Dynamic Dimension as

VDropDownSelector : Based on User Selection , it will show dimension

if( vSelector='Customer'  ,  //This is static string

([Customer]) // This is Dimention

,

if( vSelector='Region' ,

[Region]

,

if(vSelector='Country',

[Country]

)))

I am trying below expression

using

Dimension : $(VDropDownSelector)

Sum(aggr(

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

,

$(VDropDownSelector)

))

But i am getting zero . Please provide your valuable comments.

[prashantsanchet]

Hi Team,

Thank you . This is working when i used a static dimension in Aggr. But  I have requirement where

i will have a dropdown selctor like (Dynamic Dimention Name)  like Customer, Region, Country and it will be stored in variable

vSelector : This will have static string 'Customer' ,'Region,' 'Country'

I have Variable storing Dynamic Dimension as

VDropDownSelector : Based on User Selection , it will show dimension

if( vSelector='Customer'  ,  //This is static string

([Customer]) // This is Dimention

,

if( vSelector='Region' ,

[Region]

,

if(vSelector='Country',

[Country]

)))

I am trying below expression

using

Dimension : $(VDropDownSelector)

Sum(aggr(

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

,

$(VDropDownSelector)

))

But i am getting zero . Please provide your valuable comments.