Fractile and set Analysis - year range - Qlik Community

Mel7520 · ‎2024-12-19

I'm working on a table in my Qlik Sense app that displays payment delays (Délai_Paiement_Soins) by year and decile. I want to retrieve the payment delay values for each decile within each year.

this my dimension code expression :

=Aggr(

if(Sum([Délai_Paiement_Soins]) <= fractile(TOTAL Aggr(Sum([Délai_Paiement_Soins]), Decompte_unique), 0), 0,

if(Sum([Délai_Paiement_Soins]) <= fractile(TOTAL Aggr(Sum([Délai_Paiement_Soins]), Decompte_unique), 0.1), 1,

if(Sum([Délai_Paiement_Soins]) <= fractile(TOTAL Aggr(Sum([Délai_Paiement_Soins]),Decompte_unique ), 0.2), 2,

if(Sum([Délai_Paiement_Soins]) <= fractile(TOTAL Aggr(Sum([Délai_Paiement_Soins]), Decompte_unique), 0.3), 3,

if(Sum([Délai_Paiement_Soins]) <= fractile(TOTAL Aggr(Sum([Délai_Paiement_Soins]), Decompte_unique), 0.4), 4,

if(Sum([Délai_Paiement_Soins]) <= fractile(TOTAL Aggr(Sum([Délai_Paiement_Soins]), Decompte_unique), 0.5), 5,

if(Sum([Délai_Paiement_Soins]) <= fractile(TOTAL Aggr(Sum([Délai_Paiement_Soins]), Decompte_unique), 0.6), 6,

if(Sum([Délai_Paiement_Soins]) <= fractile(TOTAL Aggr(Sum([Délai_Paiement_Soins]), Decompte_unique), 0.7), 7,

if(Sum([Délai_Paiement_Soins]) <= fractile(TOTAL Aggr(Sum([Délai_Paiement_Soins]), Decompte_unique), 0.8), 8,

if(Sum([Délai_Paiement_Soins]) <= fractile(TOTAL Aggr(Sum([Délai_Paiement_Soins]), Decompte_unique), 0.9), 9, 10)))))))))), Decompte_unique)

and this is my measure code expression :

Max({<[Année Soins]={'2020'}>} [Délai_Paiement_Soins])

ofc i change the year for each section but the value aren't right and seems to be cumulated for the last fractile

i think my set analysis structure isn't good in this case. However, I'm struggling to correctly display the payment delay values for each combination of year and decile. The values seem to be cumulative across years instead of being specific to each year.Could someone please help me modify my expression or suggest an approach to correctly display the payment delays for each decile within each individual year? I need to ensure that the values are not cumulative and are properly filtered by both year and decile.Thank you in advance for your assistance!

sbaro_bd · ‎2024-12-20

Hello @Mel7520 ,

Not sure to understand what you want to do exactly, but why you keep this statement [Année Soins]={'2020'} in your set analysis if you want to show the figures for all years (in a pivot table)?

If you can provide us an application with the data! Tell us what you obtain and what did you expect as results.

Regards.

Kushal_Chawda · ‎2024-12-20

@Mel7520 can you share sample data with expected output in excel?

Mel7520 · ‎2024-12-23

I create one measure for each year so the others set is like ={'2021'} ; ={'2022'} etc. I'm not allowed to share the data set sorry. I just want to sorted delay of paiements by decil/fractil and année soins (year of care) even if you can provide me a solution which suppose that i should do one table for each year is ok for me.

Mel7520 · ‎2024-12-23

sorry i'm not allowed to share it but if you have some informations about how i can sort by year the delay of paiements it will be very helpfull for me. thanks a lot

brunobertels · ‎2024-12-23

Hi

May be Add year dimensin in your aggr function

Aggr(