Show only latest quarter data in piot table

[sundarakumar]

Hi All,

PFA a sample app that I have bee working,

this is how my data is . My requirements is always show the latest quarter value at any time i.e

when I have all expanded show all data as such, when I expand it to only year level show me the data of the latest quarter alone(40,40,20) and when I didn't even expand it by year level, show me the data of the latest year and latest quarter... (20)...

I have managed to write an expression to make it work up to expression level.

if (max(qr)=4, sum({<qr={4}>}in),
if (max(qr)=3, sum({<qr={3}>}in),
if (max(qr)=2, sum({<qr={2}>}in),
if (max(qr)=1, sum({<qr={1}>}in), ))))

Please help me out to modify the expression or with a new expression for the requirement that will show me 20 if I have not expanded the pivot table by any level..

Thanks in advance.

-Sundar

[ashfaq_haseeb]

Hi,

may be like this

Regards

ASHFAQ

[sundarakumar]

Thanks for the reply Ashfaq. But I want to show all data if nothing is selected. I cannot hide the chart if no year is selected. It should show the latest quarter and latest year data according to the expansion that user make.

-Sundar

[dharmarasu]

Hi sundarakumar,

   U can try this..

   in the dimension u want to add calculeted dimension for Quarter field...here u can restrict the quarter to max quarter..

Instead of qr field in Dimension add calculted dimension and write this..

  =if(max(qr),qr)

Hope this helps...

Regards,

Dharmarasu M

[sundarakumar]

Hi Dharma,

Thanks for the reply, That would restrict the Q1,2 and 3 at all times. I want so show user Q1,2,3, data when they expand it to quarter level.

-Sundar

[CELAMBARASAN · Accepted solution]

You could try with the Dimensionality function.

Kind of

if(Dimensionality()=2, Aggr(if(Max(Total<mg,yr> qr)=qr, Sum(in)), mg, yr, qr), Sum(in))

[dharmarasu]

hi sundar...

         try this...

          in the expression,

             check conditional and write the below script

                =count(GetCurrentSelections(yr))=0

        and expression is

                = sum(in)

add another expression

           =count(GetCurrentSelections(yr))=1

           and expression is

=sum({<qr={$(=max(qr))}>}in)

hope this helps..

Regards,

Dharma    

[sundarakumar]

I resolved the issue with the dimensionality function that u have shared, thanks a lot

-Sundar