Problems with time interval month

[#1]

Hello everyone,

I just wrote about it but without success .... rephrase the question.

In an application I have to analyze the sales turnover of the past years and the current year.

For the past years I have used the following syntax

sum ({<= FV_Tipo, Year = {$ (= max ({1} Year) -2)}>} FV_Imponibile_FAT)                    turnover in 2013

sum ({<= FV_Tipo, Year = {$ (= max ({1} Year) -1)}>} FV_Imponibile_FAT)                    turnover in 2014

sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}>} FV_Imponibile_FAT)                                turnover in 2015

and until everything is right

the problem is now in turnover in 2015 there are also invoices for the current month (for example March)

I turnover 2015 (formula below)

sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}>} FV_Imponibile_FAT)

I would remove the invoices for the current month so:

sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}>} FV_Imponibile_FAT) - sum ({<= FV_Tipo, Month = {$ (vMaxMese)}>} FV_Imponibile_FAT)

What should I put in place of the part highlighted in red so that the chief and take off from the total invoices for the current month?

Someone help me would know ... it's a bit urgent!

Thanks to all in advance.

[rubenmarin]

Hi Gian Paolo, sometimes using month in set analysis you're filtering by number when QV expects a string, and sometimes you're filtering by string and QV expects a number.

If both are string you'll need to add quotes:

sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}>} FV_Imponibile_FAT) - sum ({<= FV_Tipo, Month = {'$ (vMaxMese)'}>} FV_Imponibile_FAT)

If QV expects a number you should change vMaxMese variable to return a number or create another variable that returns a number, then you can simplify your expression:

sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}, Month = {"<$ (vMaxMese)"}>} FV_Imponibile_FAT)

[#3]

Hi Ruben,

Thanks for the answer.

I start with this formula

sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}>} FV_Imponibile_FAT)

which I think is correct.

I want to remove from this value the INVOICES (FV_Imponibile_FAT) in March of 2015

How do I?

Thanks

Gian Paolo

[rubenmarin]

Hi Gian Paolo, you tried my previous suggestions?

You can add as expression without title and see in column title how QV is translating the $-expansion variables

[#5]

Hi Ruben,

I want to use this formula

= sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}>} FV_Imponibile_FAT) - sum ({<= FV_Tipo, Month = {$ (vMaxMese)}>} FV_Imponibile_FAT)

but instead of the part in bold putting in March 2015, or better yet the current month and year.

It is clear?

Thanks

Gian Paolo

[rubenmarin]

Hi, only guessing but maybe:

sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}>} FV_Imponibile_FAT) - sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}, Month = {'$ (vMaxMese)'}>} FV_Imponibile_FAT)

or

sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}>} FV_Imponibile_FAT) - sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}, Month = {'$ (=vMaxMese)'}>} FV_Imponibile_FAT)

or

sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}, Month = {"<$ (vMaxMese)"}>} FV_Imponibile_FAT)

or

sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}, Month = {"<$ (=vMaxMese)"}>} FV_Imponibile_FAT)

[#7]

you can change the syntax in this formula and say less the current month?

sum({<FV_Tipo=, Anno={$(vMaxAnno)}, MeseNum={"<$(vMaxMese)"}>} FV_Imponibile_FAT)

If "vMaxMese" is March insert for exemple  "vMaxMese -1" until february .... if possibile but i no expert of Qlik View 

[rubenmarin]

Hi Gian Paolo, that formula is asking less the current month: MeseNum={"<$(vMaxMese)"}, if you want previous expressions to do the same you can add this filter in those expressions:

sum({<FV_Tipo=, Anno={$(vMaxAnnoPrec)}, MeseNum={"<$(vMaxMese)"}>} FV_Imponibile)

And if you want always the cummulative you can add the clause to avoid the mese filter:

sum({<FV_Tipo=, Anno={$(vMaxAnnoPrec)}, MeseNum={"<$(vMaxMese)"}, Mese>} FV_Imponibile)

This will return the same result if the user selects 'jan and feb' and if the user select only 'feb'

[#9]

It does not work!!!  sorry sorry sorry  i not understand .....

sum({<FV_Tipo=, Anno={$(vMaxAnno)}, MeseNum={"<$(vMaxMese)"}>} FV_Imponibile_FAT)

all invoices of 2015

and ok work

But I want all 2015 unless the current month .... which formula? i do not understand

sum({<FV_Tipo=, Anno={$(vMaxAnno)}, MeseNum={"<$(vMaxMese)"}, 1 and 2 >} FV_Imponibile_FAT)

sum({<FV_Tipo=, Anno={$(vMaxAnno)}, MeseNum={"<$(vMaxMese)"}, gen and feb >} FV_Imponibile_FAT)

sum({<FV_Tipo=, Anno={$(vMaxAnno)}, MeseNum={"<$(vMaxMese)"}, jan and feb >} FV_Imponibile_FAT)

On my example does not work???

Thanks

[#10]

I would use this formula: all invoices 2015 - invoices current month

sum({<FV_Tipo=, Anno={$(vMaxAnno)}, MeseNum={"<$(vMaxMese)"}> } FV_Imponibile_FAT) - sum({<FV_Tipo=, Anno={$(vMaxAnno)}, MeseNum={$(vMese=mar)} FV_Imponibile_FAT)

In the expression in bold what I have to put to dump the current month?