Limit Dimension in pivot table for date

[#1]

Hi guys,

I have read lots of discussions about limiting dimension in pivot table, anyhow I need to ask:
How can I limit dimension in pivot table to show expressions just for latest 10 dates?

Thank for your valuable help!

[MK_QSL]

Consider that your date format in QlikView main tab (system variable) as below..

SET DateFormat='DD/MM/YYYY';

Now you have Date as DD-MM-YYYY from your database.. You need to make sure that either your Database date need to be converted as per your QlikView Script Base Date or your QlikView DateFormat should match as per your database date.

QlikView Format to Database

SET DateFormat='DD-MM-YYYY';

Database Format to QliView format

Here Date is considered as string as it has different format compare to QlikView Date.

Date# function is used to convert string to number conversion.

i.e.

Date#(YourDatabaseDate, 'DD-MM-YYYY')

Now, using Date function, we can convert to our script date format. So final function looks like as below..

Date(Date#(YourDatabaseDate, 'DD-MM-YYYY')) as YourNewDateField

if you want to make different format, you can do as below (i.e MM/DD/YYYY or DD.MM.YYYY)

Date(Date#(YourDatabaseDate, 'DD-MM-YYYY'),'MM/DD/YYYY') as YourNewDateField

or

Date(Date#(YourDatabaseDate, 'DD-MM-YYYY'),'DD.MM.YYYY') as YourNewDateField

Same way we have to work as TimeStamp#, Time# also.

Hope this helps...

[#3]

Thank you!
My question from previous post persists: What causes ordering in your example?

[MK_QSL]

Dear Marek,

I didn't understood your question... sorry

[#5]

Dismiss it, please

Ok, So I run into this scenario:

For particular client, Exec_dates are numbered by AutoNumber like this:

and this is alongside with calculated dimension in pivot table:

if(EXEC_DATE_ID> 9, EXEC_DATE)

resulting in listing just 8 rows because as you can see, just fulfills this condition.

In this case there is exactly 10 dates selectable, but there could be the case of 20...
so how to restrict this to latest 10 while not being tied to ID numbering 1..10 ?

Thanks for your effort!

[MK_QSL]

Can i have your sample apps please.. .rather than working on my own data set, if you provide us your sample data in excel file would be better to work..

[#7]

ahh, that would be too tough to export all related data to achieve selections like I have and change the values manually in order to hide a real values.. And I can't send you a qvw that I am working on, that would be against the company's policy...
Can you just point me to the right path? I think now you understand my context.

Thank you!

[MK_QSL]

But you can create a sample data having 30-40 dates ....

[arjunkrishnan]

Dimension Not Available in Pivot Table It's Only Available In Straight Table ....

If U want Limited Your Record Write Expression Based On Condition...

[#10]

thank you
I understand that this post is too long but if you would take a little piece of your valuable time and scroll down a bit you would see that this is what we are trying to do.

[#11]

If you are good enough with dates, be my guest, but... I don't think this will help too much.

Again, Im sorry but I just can't  modify all the data for you to be able to make selections in all dimensions for you to be able to see what I am...
So basically, by taking the approach of numbering the exec_dates by AutoNumber() funct we are running to scenario where one can't anticipate exec_dates_ids to be sorted nor continual. Is there some other approach?