Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in.
**SIGN UP NOW**

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- App Development
- :
- Re: Measure not aggregated in Pivot Table

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

RighettiAndrea

Contributor III

2022-02-23
09:53 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Measure not aggregated in Pivot Table

Hi everyone,

I have a pivot table with 2 dimensions.

As you can see here, the column "Quantità" can be related to every dimension, while the other one ("Ton") is only related to the Date. I'd like to have only the measure called "Qta / Ton" but I'm not able to get the right value.

What I'd like to see is a calculation based on the measure Quantità divided by the total Ton (49377).

For example I want to see in the first row (ECOGARDA SRL) the result of 228260/49377 = 4.62.

Here the measure Quantità

Sum({Acquisti <MonthDiff={"<12"}>} [Righe.Quantità])

And here the measure Ton

Aggr(Sum({<MonthDiff={"<12"}>} [S_ODL.Lordo Fonderia]/[DT_ARTICOLI.Impronte]*[DT_ARTICOLI.Peso Stampata])/1000,AsOfMonth)

How can I get the new measure "Quantità / Ton" as explained before?

Thanks

608 Views

1 Solution

Accepted Solutions

RighettiAndrea

Contributor III

2022-02-24
03:15 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi everyone!

I've found a soulution.

Sum({Acquisti <MonthDiff={"<12"}>} [Righe.Quantità]) /

Aggr((Sum(Total <AsOfMonth> {<MonthDiff={"<12"}>} [S_ODL.Lordo Fonderia]/[DT_ARTICOLI.Impronte]*[DT_ARTICOLI.Peso Stampata])/1000),AsOfMonth,Fornitori.Fornitore)

It is based on Mayil's answer.

Thanks a lot!!

5 Replies

Aurelien_Martinez

Partner - Specialist II

2022-02-23
09:58 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi,

You can try to add your first dimension in the aggr like :

Sum(Aggr(Sum({<MonthDiff={"<12"}>} [S_ODL.Lordo Fonderia]/[DT_ARTICOLI.Impronte]*[DT_ARTICOLI.Peso Stampata])/1000, AsOfMonth, [Fornitori.Fornitori]))

Aurélien

Help users find answers! Don't forget to mark a solution that worked for you!

601 Views

RighettiAndrea

Contributor III

2022-02-24
02:09 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi,

unfortunately it doesn't do what I'm looking for.

If I write the complete measure this way (here below), the numbers aren't correct

Sum({Acquisti <MonthDiff={"<12"}>} [Righe.Quantità]) /

Sum(Aggr(Sum({<MonthDiff={"<12"}>} [S_ODL.Lordo Fonderia]/[DT_ARTICOLI.Impronte]*[DT_ARTICOLI.Peso Stampata])/1000,AsOfMonth,[Fornitori.Fornitore]))

567 Views

MayilVahanan

MVP

2022-02-24
02:22 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi

Might be, try like below

"Quantità / Ton":

Sum({Acquisti <MonthDiff={"<12"}>} [Righe.Quantità]) /

Aggr(Sum(Total {<MonthDiff={"<12"}>} [S_ODL.Lordo Fonderia]/[DT_ARTICOLI.Impronte]*[DT_ARTICOLI.Peso Stampata])/1000,AsOfMonth)

or

Sum({Acquisti <MonthDiff={"<12"}>} [Righe.Quantità]) /

Aggr(Sum(Total<urDim> {<MonthDiff={"<12"}>} [S_ODL.Lordo Fonderia]/[DT_ARTICOLI.Impronte]*[DT_ARTICOLI.Peso Stampata])/1000,AsOfMonth)

Thanks & Regards, Mayil Vahanan R

Please close the thread by marking correct answer & give likes if you like the post.

Please close the thread by marking correct answer & give likes if you like the post.

561 Views

RighettiAndrea

Contributor III

2022-02-24
02:37 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi,

neither this unfortunately.

I'll write my calculated dimension here below (I don't know if this can help)...the other one is not calculated.

=If(Year(AsOfMonth)>Year(Now())-3,AsOfMonth)

I'll show you what I get with both the solutions you wrote.

In the first case I don't get the column Fornitori.Fornitore and the Total show is uncorrect.

In the second case, replacing <urDim> with AsOfMonth I don't get the column Fornitori.Fornitore but the Total is correct.

thanks a lot

554 Views

RighettiAndrea

Contributor III

2022-02-24
03:15 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi everyone!

I've found a soulution.

Sum({Acquisti <MonthDiff={"<12"}>} [Righe.Quantità]) /

Aggr((Sum(Total <AsOfMonth> {<MonthDiff={"<12"}>} [S_ODL.Lordo Fonderia]/[DT_ARTICOLI.Impronte]*[DT_ARTICOLI.Peso Stampata])/1000),AsOfMonth,Fornitori.Fornitore)

It is based on Mayil's answer.

Thanks a lot!!

Community Browser