last 12 moths columns

[prabhas277]

HI experts,

I have one pivot table with number of columns ..now my requirement  is based on selection i want see the last 12 columns in the table..

How i can get this?

Thanks in advance..

[Chanty4u]

Hi,

Am not sure try below

=last( sum( Sales ),10 )

[Anil_Babu_Samineni]

Your question is not ful filled. As i assume are you expecting similar this // What does mean for last 12 Columns

[prabhas277]

Thanks for reply

NO.

For example i have columns name like name, sales1,sales2.... upto sales30.

now based on selection i want see only 12 columns like sales1,sale22...upto sales12.

[Chanty4u]

try to check this once am not sure this is what you are looking or not?

Table or Pivot with dynamic columns

[benjamins]

In general, this is what the "Conditional" checkbox on the expressions tab of each chart is for.

As your "based on selection" is a pretty generic requirement I am afraid I'm not able to give a very specific answer, but here is the general idea:

Say you select a value for variable var1, then the condition for the sales1 column is:

$(var1) - 12 < 1 and $(var1) > 0

The condition for sales14 is:

$(var1) - 12 < 14 and $(var1) > 13

And so on.

So if you set the variable to 24, you will see all columns from Sales13 to Sales24, as

Sales 13: 24 - 12 < 13 and 24 > 12 is true

...

...

Sales 24: 24 - 12 < 24 and 24 > 23 is also true

all other columns are false

[Anonymous]

What does the number represent?

Is Sales1 = Sales for week 1?

& Sales5 = Sales for week 5?

and the value of 1...n held in your data structure?

Maybe post some sample data and a screenshot of your pivot?

[hemanthaanichet]

Hi Prabhas,

sum({<Year={$(=only(Year))}, month={"$(<=num(month))"]>}Sales])

or

=if(((MakeDate(Year,Month) < MonthStart(Today())) and (MakeDate(Year,Month) >=AddMonths(MonthStart(Today()),-12))),
Month(MakeDate(2000, Month, 1)) & ' ' & Year)

Regards

Hemanth

[susovan]

Hi prabhas,

May be like this,

[chhavi376]

Hi Prabhas,

As per my understanding, you need the last 12 records for sales(sales 1, sales 2, ... sales 30), on a particular selection of a field.

Say, we have 30 records, and on one particular selection you want sales 18,sales 19.... sales 30 and if not, then it shows all the records.

I have made a variable, which keeps a track that which is the 12th record from the last, and comparison has been made, so that only the last 12 records are displayed.

there is an Inline field(SHOW), which has 2 entries, show all, and show last 12.

If we click on show last 12, we are getting the desired result.

I am attaching the QV and the sample datasource.

please have a look at the variables and the expressions.

if not fulfilled, please attach a sample here for better clarity.

Thanks