Previous Function in script

[Marcos112]

Hello Everyone

Please, I need the previous function to give me the same result as above. As you can see, the objective is for the sales of one month to be seen in the next. The problem above : (above(sum([PRONOSTICO.NUM_MONTO_FINAL]),1)) is that when I select a period I lose the previous month's, that's why I wanted to use previous but it is not working. attached to the script

Thansk in advance

[Marcos112]

@Kushal_Chawda Can you help me with that?

[Kushal_Chawda]

@Marcos112  If you want to do it in script, first you need to aggregate your data with dimensions you have used in your report. But, you can still achieve it in front end with below approach

=sum(aggr(above(total Sum({<Periodo,[PRONOSTICO.DSC_CANAL],%ADM_ITEM>}[PRONOSTICO.NUM_MONTO_FINAL])),1),

([PRONOSTICO.DSC_CANAL],(TEXT,ASCENDING)),(Periodo,(TEXT, ASCENDING)),%ADM_ITEM))

In aggr function I have defined the sorting of your field which you need to change according to your requirement.  Sorting sequence in aggr should match with sorting defined in the report properties. for eg. sorting sequence of your report in the property is 1) Periodo 2) [PRONOSTICO.DSC_CANAL] then in aggr you need to change the sequence and your expression will become as follows

=sum(aggr(above(total Sum({<Periodo,[PRONOSTICO.DSC_CANAL],%ADM_ITEM>}[PRONOSTICO.NUM_MONTO_FINAL])),1),

(Periodo,(TEXT, ASCENDING)),([PRONOSTICO.DSC_CANAL],(TEXT,ASCENDING)),%ADM_ITEM))

Also, make sure that according to your requirement you can change the sorting option in aggr for eg.  ASCENDING or DESCENDING refer to the aggr parameters from below link

https://help.qlik.com/en-US/sense/2.2/Subsystems/Hub/Content/ChartFunctions/aggr.htm?_ga=2.269114358...

[Marcos112]

@Kushal_Chawda  Thanks a lot, 

Can you help me with this? I don't understand why it's not taking the asc parameter and give me error

[Kushal_Chawda · Accepted solution]

@Marcos112  i think there is additional bracket in expression.. try below

=sum(aggr(above(total Sum({<Periodo,[PRONOSTICO.DSC_CANAL],%ADM_ITEM>}[PRONOSTICO.NUM_MONTO_FINAL]),1),

(Periodo,(TEXT, ASCENDING)),([PRONOSTICO.DSC_CANAL],(TEXT,ASCENDING)),%ADM_ITEM))

[Marcos112]

@Kushal_Chawda  Response to your question, the only dimension i will need are  Periodo , %Adm.Rubro,  and %Adm.Item these in the pivot table like you see below

[Marcos112]

@Kushal_Chawda Thanks a lot , I made some variants and its works.