Pivot table condition

[deepakqlikview_123]

Hi all,

PFA.

Can you please suggest how to implement column for > 1 year.

Because I have MonthYear column I am able to get all view after pivoting month year column except > 1 year column

[a_mullick]

Hi,

A possibility, but perhaps not as flexible, would be to create a parallel column in your ETL script, which has the same value for MonthYear for the 1st 12 months, but labels every MonthYear after that as '>1 Year'. You could then pivot on this new column.

Thanks,

Azam

[deepakqlikview_123]

Hi all,

Please find attached sample QVW for this can you please suggest how to add>1 year column in my pivot table.

Thanks

[rahulpawarb]

Hello Deepak,

Trust that you are doing good!

As a workaround you can opt for calculated dimension for AR_CustomerLedger.MonthYear field. Below is sample expression.

=If(Date(AR_CustomerLedger.MonthYear, 'MMM-YYYY') >= Date(AddMonths(Today(),-13), 'MMM-YYYY'), Date(AR_CustomerLedger.MonthYear, 'MMM-YYYY'), '> 1 Year')

Also refer sample application attached herewith.

Hope this will be helpful.

Regards!

Rahul

[deepakqlikview_123]

Hi Rahul,

Thanks for the reply but i need to add >1 year column in my chart . can you please suggest after pivoting one of dimension in pivot table how is it possible.

Can you Please refer my previous communication.

Thanks

[rahulpawarb]

Hello Deepak,

Presently, table AR_CustomerLedger has data for 12 months only. Therefore, pivot tables do not show column for "> 1 Year". If you reload the application with more than 12 months data then you will see data for "> 1 Year".

BTW, I have modified the existing application for > 6 Months .

Please correct me If I misunderstood.

Regards!

Rahul

[deepakqlikview_123]

Hi Rahul,

I have attached sample app for reference .

Can you please suggest.

I need >1 year column view only for one selection 1 year data view in drop down box so that I am able to view 12 months data and >1 year column.

Thanks

[deepakqlikview_123]

Experts,

My requirement is as shown in attachment only for option 1 year selection > 1 year column should be displayed in chart.

I am using  below condition in my expression.

pick(Match([Not Paid in Last],'3 months','6 months','9 months','1 Year','> 1 Year'),Sum({<AR_CustomerLedger.MonthYear ={">=$(=Date(addmonths(Max(AR_CustomerLedger.MonthYear), -2), 'MMM-YYYY'))"} >} AR_CustomerLedger.OpenAmount_Resolver),

Sum({<AR_CustomerLedger.MonthYear ={">=$(=Date(addmonths(Max(AR_CustomerLedger.MonthYear), -5), 'MMM-YYYY'))"} >} AR_CustomerLedger.OpenAmount_Resolver),

Sum({<AR_CustomerLedger.MonthYear ={">=$(=Date(addmonths(Max(AR_CustomerLedger.MonthYear), -8), 'MMM-YYYY'))"} >} AR_CustomerLedger.OpenAmount_Resolver),

Sum({<AR_CustomerLedger.MonthYear ={">=$(=Date(addmonths(Max(AR_CustomerLedger.MonthYear), -11), 'MMM-YYYY'))"} >} AR_CustomerLedger.OpenAmount_Resolver),

Sum({<AR_CustomerLedger.MonthYear ={">=$(=Date(addmonths(Max(AR_CustomerLedger.MonthYear), -20), 'MMM-YYYY'))"} >} AR_CustomerLedger.OpenAmount_Resolver)

)

Can you please suggest how to sort this app in descending order

Thanks

[deepakqlikview_123]

Hi Rahul,

In your case it is showing the column as per requirement but in generated >1 year column value and >1 year view value in list box selection is not matching.

Thanks

[rahulpawarb]

Hello Deepak,

Thank you sharing your views.

Could you please elaborate more on this? Please share the updated application. This will help me to analyze the issue and provide your my feedback.

Regards!

Rahul