Regarding my solution to the logic

[#1]

Hello Friends,

I appreciate your time, kindly let me I am in a right logic or not.

I have fields such as PERIODSTARTDATE,PERIODENDDATE,PAIDAMOUNT,REBATESUBTYPE,Product Family, Plan name, Contractid and contract name in my table

I got the requirement as below:

Rebate 1Q_Year:

Derive Quarter and Year based on PERIODSTARTDATE and PERIODENDDATE; then sum PAIDAMOUNT where  REBATESUBTYPE is anything other Admin Fee

My solution:

I have created a pivot table and add the  dimension fields as REBATESUBTYPE,Product Family, Plan name, Contractid,contract name and in my expression I did the following:

=sum(if([REBATESUBTYPE] <> 'Admin Fee' and [CalendarQuarter] ='Q1',[PAIDAMOUNT]))

I already derived the [CalendarQuarter] and [CalendarYear] based on PERIODSTARTDATE and added them as a list box filters.

Kindly let me know my logic is right because I need to derive a couple of other logics very similar the same.

Kind regards,

Kiru

[#2]

Hi Kiru,

You can use set analysis

=sum({<[REBATESUBTYPE] ={'*'}-{ 'Admin Fee'},  [CalendarQuarter] ={'Q1'} >} [PAIDAMOUNT])

[#3]

Thanks Hemanth, I appreciate your time.

sure of-course  that is an alternative solution,

But  kindly I would like to know my logic is right or not because I am much comfortable without set analysis

Kind regards,

Kiru

[#4]

Two points. See if this fits your requirement.

1. Looks like you have [REBATESUBTYPE] as dimension, So you can make it as calculated dimension as if([REBATESUBTYPE]<>' Admin Fee',[REBATESUBTYPE]) and tick the option "Suppress when value is null".

2. Assume you have [CalendarQuarter] and [CalendarYear] calculated based on PERIODSTARTDATE in the load script itself.

However do you want to create a pivot table for each quarter?  why not adding the CalendarQuarter and CalendarYear as dimensions.

So that after above changes, you can have a simple expression as below.

sum( [PAIDAMOUNT])

[#5]

Thanks Hemanth,

I think I am good with my logic as of now by using

=sum({<[REBATESUBTYPE] ={'*'}-{ 'Admin Fee'},  [CalendarQuarter] ={'Q1'} >} [PAIDAMOUNT])

for the requirement

Derive Quarter and Year based on PERIODSTARTDATE and PERIODENDDATE; then sum PAIDAMOUNT where  REBATESUBTYPE is anything other Admin Fee.

because as per the UI ,I need to display only Contractid ,contract name and product name and then comes the expression which I wrote above.

I have derived the Quarter and year from script. So I am going with my script expression for now for dev data and if any problem exists then let me change it.

Thanks and I appreciate your time.

Kiru