Optimize the speed of load of a pivot table

[caccio88]

Hi guys,

who could help me please.

In my QV there a pivot table that's made by a ValueList as dimension.

So there are so many expressions based on that ValueList.

The problem is that cause a QV so slow and when u load the file and when u make a filter.

I attach here my QV, "Labour Cost_2", to take a look at the script of the expressions in the pivot table.

My own goal would to make quicker the app. Maybe changing the formula of the expressions without using the ValueList or with others solution.

Thank u so much.

[Gysbert_Wassenaar]

Change your data model so that the values you now have in the valuelist are in a table and linked to the appropriate accounting items. Perhaps this blog post can help: How IntervalMatch Solved My Profit and Loss Dilemma

[caccio88]

Hi gwassenaar‌ and thaks for your help.

But I'm not so sure to understand what's your solution related to my QV.

Could u please explain me your solution in another way, please?

I just have watched the video of How IntervalMatch Solved My Profit and Loss Dilemma‌ , but I can't understand how IntervalMatch could solve my optimization.

Thank u

[Gysbert_Wassenaar]

Start by creating a table in the script with the values in your valuelist and the accounting items that should be grouped with those items:

LOAD * INLINE [

Group, Accounting Item

399-TOT. RETRIBUZIONE LORDA. 399-TOT. RETRIBUZIONE LORDA

1999-TOT. RETRIBUZIONE IMPONIBILE,399-TOT. RETRIBUZIONE LORDA

1999-TOT. RETRIBUZIONE IMPONIBILE,Festività (lavorate)

1999-TOT. RETRIBUZIONE IMPONIBILE,Indennità varie

1999-TOT. RETRIBUZIONE IMPONIBILE,Straordinari

1999-TOT. RETRIBUZIONE IMPONIBILE,Premi/UT

1999-TOT. RETRIBUZIONE IMPONIBILE,Assenteismo

1999-TOT. RETRIBUZIONE IMPONIBILE,Altri Bdg Old

...etc

];

Then use Group as dimension in your pivot table. And you can use a simpler expression like

num(Sum({$<[HQ/BU]={'Unit'},DateYearMonthID="$(=Max(DateYearMonthID))"},DateFiscalYear=,DateFiscalQuarter=,DateMonth=>} Value),'#.##0')

You don't have to use an inline table. You could create it in an excel file too and load it from the excel file. Or perhaps you already have that information available in another source you can load from.

[caccio88]

Right than u so much gwassenaar . I think that is my solution but I've met a new problem...

That's my exact load inline:

LOAD * INLINE [

    Group, Accounting Item

    399-TOT. RETRIBUZIONE LORDA, 399-TOT. RETRIBUZIONE LORDA

    1999-TOT. RETRIBUZIONE IMPONIBILE, 1999-TOT. RETRIBUZIONE IMPONIBILE

    3299-TOT. CONTRIBUTI, 3299-TOT. CONTRIBUTI

    4099-TOT. TFR, 4099-TOT. TFR

    4599-TOT. ACCANTONAMENTI, 4599-TOT. ACCANTONAMENTI

    4999-COSTO GENERALE, 4999-COSTO GENERALE

    5101-COSTO GENERALE, 5101-COSTO GENERALE

    TOTALE X CDG, TOTALE X CDG

    COOPERATIVE, PULIZIE AFFIDATE A TERZI

    COOPERATIVE, PERSONALE COOPERATIVE PALMAR

    PRESTATORI DI OPERA, PRESTATORI DI OPERA

    TOTALE MAP, TOTALE X CDG

    ORE LAVORATE DIPENDENTI, TOTALE ORE EFF. LAVORATE (RIPARTITE)

    COSTO MEDIO ORARIO DIPENDENTI, TOTALE X CDG / TOTALE ORE EFF. LAVORATE (RIPARTITE)

    ORE LAVORATE PRESTATORI DI OPERA, Ore Lavorate Interinali

    COSTO MEDIO ORARIO PRESTATORI DI OPERA, PRESTATORI DI OPERA / Ore Lavorate Interinali

];

As u can see "COSTO MEDIO ORARIO DIPENDENTI" and "COSTO MEDIO ORARIO PRESTATORI DI OPERA" are derived from a division. But with this script the formula doesn't work...

How can I solve it??

[caccio88]

Do u think that is possible to get around the obstacle gwassenaar‌?

[Gysbert_Wassenaar]

Yes, I think it's possible. I did something like this not too long ago. I'll see if I can find it again.

[caccio88]

Wonderful. I hope so. thanks gwassenaar

[Gysbert_Wassenaar · Accepted solution]

See this discussion: Year over Year (YOY) comparison based on Dimensions

[caccio88]

Yep, gwassenaar‌! Fantastic! That's exactly what I was looking for! You've gave me the chance to optimize so many application with this new concept! You opened my QV's "script mind"! I've just advanced your suggestion with others fields and I've been able to do wonderful things!

Thank u legend! So grateful!