Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Contributor III
Contributor III

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)

                         )