summing data for selected period

[MadKow19]

hi,

I have an app that contains data from 2017 to date. It contains months, accounting accounts, value, etc.
In the application, a filter field is inserted: month.

The value of the expression :
(Sum({<KONTO={"701*", "702*", "705*", "706*"}>} (KWOTA_MA))
+ Sum({<KONTO={"730*", "732*", "734*"}>}(KWOTA_MA))
+ Sum({<KONTO={"601*", "602*"} >}(KWOTA_WINIEN))
- Sum({<KONTO={"601*", "602*"} >}(KWOTA_MA)
+ Sum({<KONTO={"791*", "792*"} >}(KWOTA_MA))
+ Sum({<KONTO ={"400*","401*","402*","403*","404*","405*","408*","409*","410*","411*"} >}-(KWOTA_WINIEN))
+ Sum({< KONTO={"735*", "737*", "739*"}>}-(KWOTA_WINIEN))))

my table:

LOAD

    NUMER_OKRESU,

     NAZWA_OKRESU,

    Date(Date#(NAZWA_OKRESU,'MMMM YYYY'),'YYYY-MMMM')             as DATA,

    Year(Date#(NAZWA_OKRESU,'MMMM YYYY'))                                             as DataRok,

    num(Month(Date#(NAZWA_OKRESU,'MMMM YYYY')))                            as DataMiesiac,

    KONTO, 

    NAZWA_SKROCONA,

    KWOTA_WINIEN,

    KWOTA_MA

;

After selecting a month in the filter panel, I would like to get a total for the selected date and 11 months back. 
What and where should I enter?

Magda

[GaryGiles]

It looks like your month field, DataMiesiac only returns the month number, regardless of the year.  Is that the month field in your filter pane?  If so, how do you know what year is being selected?

If you are filtering the date via the DATA field, you could add the following criteria to the set analysis of each sum() limit to the previous 11 months of the selected date:

In each sum statement, add the following criteria to your set analysis: 

DATA={"<=$(=max(DATA))>=$(=AddMonths('$(=max(DATA))',-11))"}

For example:

Sum({<DATA={"<=$(=max(DATA))>=$(=AddMonths('$(=max(DATA))',-11))"},KONTO={"701*", "702*", "705*", "706*"}>} (KWOTA_MA))

[MadKow19]

Hi,

thank you for your reply. I took your solutions to my applications and it doesn't work.

I used the DATE field in the filtering.

I think the problem is in the DATE field. It is somehow the difference between filtering the DATE field and the DATA field in my table.

I found another statement:

sum({< PeriodFilter=, [Period ID]={">=$(=max([Period ID])-12)<=$(=max([Period ID]))"}>} Sales)

but I don't know where I have Period ID or PeriodFilter field

[berkarmagan]

Do you have a calander table in your model, if you so you should add a calculated flag column for your period you want, i mean a colomn which contains 1 if the date is in 11 months then you can use that flag in your expression 

[MadKow19]

Hi, yes, in my app I have calendar for field DATA, and sorry,  I don't know what is flag column. Where I have to put in on. How to create it?  I'm new one in Qlik family 🙂

[berkarmagan]

Hi again,

For example in your calendar table just create a new column like;

IF(Date>=MonthStart(Today(1)-365),1,0) AS [Flag 12 months],

It will return you as 1 or 0; and you can use this Flag column in your set expression like ; sum({<[Flag 12 months] = 1, Date = , Year =, Month = >}Sales), this provides you to get last 12 months total sales.

[MadKow19]

I must do something wrong. I used  your solution in calander tabele and in set analyst. It dosen't work.

When I don't choose date in filtering field I have sum for everymonths, but when I choose filtering field in for example Febuary 2021 I have only for this months 😕

[berkarmagan]

Hi,

Did you add these red fields; sum({<[Flag 12 months] = 1, Date = , Year =, Month = >}Sales)

Because if you didnt add, the expression goes like, ok calculation filtered by your flag, but when you pick a selection on the front side, it will also effect your calculation. So you should add fields in your set expression as above which you dont want to make effect to your calculation. In this scenerio I think you should add fields related to your calendar except your flag field.

[MadKow19]

of course,

in calendar table I have:

[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS

(...)

IF(Date>=MonthStart(Today(1)-365),1,0) AS [Flag12months];

DERIVE FIELDS FROM FIELDS [DATA] USING [autoCalendar] ;

in set analyst;

sum({<[Flag12months] = 1, Date = , Year =, Month =, KONTO={"701*"} >}KWOTA_MA)

my table:

LOAD
NUMER_ROKU,
NUMER_OKRESU,
NUMER_ROKU & '-' & NUMER_OKRESU AS Numer_Roku_Okresu,
NAZWA_OKRESU,
Date(Date#(NAZWA_OKRESU,'MMMM YYYY'),'YYYY-MMMM') as DATA,
KONTO,
KWOTA_WINIEN,
KWOTA_MA,

(...)

[MadKow19]

Hi,

I found problem with Flag 12 months in my app and I correct it. Thanke for help.

Now I have another one. In your set expression: sum({<[Flag 12 months] = 1, Date = , Year =, Month = >}Sales) I want to add next data restriction
sum({<KONTO ={"701*"}, [Flag12monthsMK] = 1, Date = , Year =, Month = >}KWOTA_MA)

Is it correct?
This solution show value only for pick up month, no for 12.
But when I use only that one sum({< [Flag12monthsMK] = 1, Date = , Year =, Month = >}KWOTA_MA) is sum 12 months but for all posibility.

How to add next data restriction?

Magda