Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
MadKow19
Contributor
Contributor

summing data for selected period

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

9 Replies
GaryGiles
Specialist
Specialist

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
Contributor
Contributor
Author

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
Partner
Partner

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
Contributor
Contributor
Author

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
Partner
Partner

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
Contributor
Contributor
Author

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
Partner
Partner

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
Contributor
Contributor
Author

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
Contributor
Contributor
Author

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