Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
jimmygohhanjie
Contributor III
Contributor III

Aggr Set analysis for last day of period value

Hi again,

Require advise regards to last day of Week, Month, Quarter and Year (Period).

Some context, this is to calculate the Outstanding vol. The daily OS vol has not issue.

however when the pivot chart cyclic dimension change to Week or Month. it sum up all the OS vol.

the expected value, it should show the last day OS vol.

Daily valueCapture.JPG

Weekly Desire output Capture.JPG

Ideally the set analysis should work for Month, quater and year in the Cyclic Dimension.

MTK_DATE is the Day/Date. Week, Month, Quarter and Year are derived from MTK_DATE.

It is possible to use the below and resolve the issue?

FirstSortedValue(

                              Aggr(

                              WORK_IN - WORK_OUT

                              , MTK_DATE

                              )

                       , -Aggr(MTK_DATE, MTK_DATE)

                         )

The last solution would be create a Flag for each period, and the set anaylsis would use a couple of IF to check cyclic options and perform the right set analysis. I find that is the raw way and feel there should have a better way

Thanks for your time in this.

1 Reply
jimmygohhanjie
Contributor III
Contributor III
Author

oh,

I have sort it...

It due to another factor. another dimension in the pivot chart

FirstSortedValue(

                              Aggr(

                              WORK_IN - WORK_OUT

                              , MTK_DATE, TEAM ID

                              )

                       , -Aggr(MTK_DATE, MTK_DATE,TEAM ID)

                         )