topic Re: Pivot table condition in QlikView

Pivot table condition

deepakqlikview_123 — Fri, 17 Mar 2017 11:15:42 GMT

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

Re: Pivot table condition

a_mullick — Fri, 17 Mar 2017 12:15:29 GMT

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

Re: Pivot table condition

deepakqlikview_123 — Mon, 20 Mar 2017 06:10:10 GMT

Hi all,

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

Thanks

Re: Pivot table condition

rahulpawarb — Mon, 20 Mar 2017 06:56:32 GMT

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

Re: Pivot table condition

deepakqlikview_123 — Mon, 20 Mar 2017 08:07:48 GMT

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

Re: Pivot table condition

rahulpawarb — Mon, 20 Mar 2017 08:41:06 GMT

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

Re: Pivot table condition

deepakqlikview_123 — Mon, 20 Mar 2017 09:33:46 GMT

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

Re: Pivot table condition

deepakqlikview_123 — Mon, 20 Mar 2017 09:53:35 GMT

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

Re: Pivot table condition

deepakqlikview_123 — Mon, 20 Mar 2017 13:14:54 GMT

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

Re: Pivot table condition

rahulpawarb — Tue, 21 Mar 2017 05:29:16 GMT

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

Re: Pivot table condition

deepakqlikview_123 — Tue, 21 Mar 2017 06:08:47 GMT

Hi Rahul,

I have shared my updated app in my previous thread.need to show below view in image for 1 year drop down selection only.

Thanks

Re: Pivot table condition

rahulpawarb — Tue, 21 Mar 2017 07:23:35 GMT

Hello Deepak,

Looking into this. Soon I will provide you updates.

Regards!

Rahul

Re: Pivot table condition

deepakqlikview_123 — Tue, 21 Mar 2017 09:21:03 GMT

Thanks Rahul

Thanks

Re: Pivot table condition

deepakqlikview_123 — Tue, 21 Mar 2017 10:10:59 GMT

Hi All,

Can you please help me out on this ASAP.

Thanks

Re: Pivot table condition

Anil_Babu_Samineni — Tue, 21 Mar 2017 10:13:10 GMT

This is not working for you or what?

Re: Pivot table condition

deepakqlikview_123 — Tue, 21 Mar 2017 10:31:40 GMT

Hi anil,

Thsi is not working for me as excepted.

Thanks

Re: Pivot table condition

Anil_Babu_Samineni — Tue, 21 Mar 2017 10:39:33 GMT

As expected means, From here what are you looking to see. I am sorry if i miss something in this thread to read