Sales from previous year

[Bad_QlikDeveloper99]

Hello,

I am new to QlikView.

I have a difficult formula here with many filters for calculating our sales. I would like to know how exactly I can display this formula for the respective previous year.

Thanks a lot in advance

Formula:

Sum({$<

$(yServiceDBFilter1),

$(xvarKundeARKU),  

V_BelegPos.offen = {'0'}, 

V_BelegPos.BelegArt = {'R','G'},

S_KoTraeger.KostenTraeger = {"404","405","406","407","408","4011","4012","4021","4022","4023","4024"}

>

+

<

S_AuftragsArt.AuftragsArt={"ST","SE","SW"},

S_Artikel.ArtikelArt ={61},

$(xvarKundeARKU),  

V_BelegPos.offen = {'0'}, 

V_BelegPos.BelegArt = {'R','G'},

S_KoTraeger.KostenTraeger = {"404","405","406","407","408","4011","4012","4021","4022","4023","4024"}

> 

}

Rangesum ( V_BelegPos.xc_Gesamtpreis$(vCurrency) * V_BelegPos.GS_Faktor * V_BelegPos.ZS_Faktor

,subfield(V_BelegPos.c_Zuschlag,';',1)

,(subfield(V_BelegPos.c_Zuschlag,';',2))

,(subfield(V_BelegPos.c_Zuschlag,';',3))

,(subfield(V_BelegPos.c_Zuschlag,';',4))

,(subfield(V_BelegPos.c_Zuschlag,';',5))

)

)

[Oleg_Troyansky · Accepted solution]

Hi there,

Yes, this calculation is quite complex - perhaps more complex than it should be. It looks like it could be simplified quite a bit by using data modeling in the back end and restructuring your data a little bit. However, this is out of scope of this conversation.

Regarding the previous year - there are a few ways of doing that:

1. If you are looking for a way to calculate sales for the previous year, compared to the current year (always determined by the current date), then the best way would be to calculated a flag in your Calendar Table that would contain 1 for all the dates in the previous year and 0 or NULL for all other dates. Then you can either add the flag condition to your Set Analysis, or multiply your Sales field by the flag.

2. If you are looking to show the previous year sales for each period of time (Month, for example), then you'd need to build a so called "As of Date" table. There are several blog articles that describe this technique, including my own, which you can see here. 

To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!