How To Calculate Selected Date, Last year date, MTD,YTD

[#1]

Hi,

I am trying to achieve the following and i did something with the expressions but looks like my values are incorrect.Can someone please throw any suggestions on this?

I am trying to achieve the calculations for

1) TY Traffic ( This Year Traffic for a given date)

2) LY Traffic ( Last Year Traffic for a given date)

3) TY MTD Traffic (  This Year Month To Date( for the whole month until selected date)Traffic for a given date)

4) LY MTD Traffic (  Last Year Month To Date( for the whole month of last year until selected date)Traffic for a given date)

5) TY YTD Traffic (  This Year Month To Date( for the whole year until selected date)Traffic for a given date)

6) LY YTD Traffic (  This Year Month To Date( for the whole last year  until selected date)Traffic for a given date)

I have attached a sample excel pivot table also which helps me what values i am looking for and i have attahced my test QVW i am working on, I have selected the date as 20140323 in both QVW and excel for location 3020.So, for these conditions these 2 must match.

The MTD and YTD is same in this case as our Fiscal Month Starts in February .

Can someone please throw any suggestions.

If you have any questions please let me know.

Thanks

[#2]

Hi

Can you share the sample QVDs?

Thanks

Sabal

[#3]

Hi Sabal,

Sorry i didnt get you i dont have any sample QVW's all i have is the excel file which is attached for validation.

Please let me know if you need anything else.

Thanks,

Sujith

[#4]

Hi

I meant the QVD files from which you are loading data in QVW file in your script.

Files such as:

..\..\Data\QVD\Transform\StoreTraffic_LocWk.qvd (Qvd);

Just a small subset of data would also suffice.

Thanks

Sabal

[#5]

Hi Sabal,

Attached are the 3 QVW's  i am using in this application.

Please let me know if you need anything else.

Thanks

[#6]

Hi Sabal,

Any luck with these ? Sorry to keep you asking but i am already 2 days behind on this work and i need to get it done soon as i have 30 more calculations which basically use the same logic which i need to create.

Thanks

[preminqlik]

hi there find attachment and make variables wrto it

hover your mouse on exp to get what exactly happening.

hope this helps you

[#8]

Hi Prem,

Looks like we are getting what we want, but in the facts tab if i add sales to traffic the values seems to be getting higher, i think its doing some cross join. Can you please help me with the correct way to write the script for load script to join multiple facts into one?

Attached is my updated QVW.

Also, looks like we are having the calculations for

TY Traffic,

TY MTD Traffic,

LY MTD Traffic,

TY YTD Traffic Traffic,

LY YTD Traffic

I think we are missing LY Traffic, can you also please help me with the calculation for Last Year Traffic.( missing i mean like the value is showing up as 0, where as we do have the value for 2013 traffic)

I really appreciate your time and help.

Thanks

Thanks

[#9]

Hi

Sorry for the delay in reply.

Had left from Office.

PFA the QVW file with the revised formulae.

Earlier I had not taken into account the fact that the first day of the week shows the traffic for whole week.

Let me know if still you face any issues.

Thanks

Sabal

[#10 · Accepted solution]

Hi Sabal,

Thanks a lot for your help, it works perfectly fine.However i have 2 last things i want to know,

1) if you look at the attached file in the facts tab i have added sales too but looks like some cross join is happening which is giving wrong values, so can you please help me in changing my load script such that i can add multiple facts to a single one and join them?I just have traffic and sales added for now but i have to add more.

2) the below set analysis

sum({<TransactionChannelDesc ={POS},TransactionTypeId = {1}>}[Units])

gives me the units for POS and tran type 1 what ever we select, however this one will be just for a single day, Can you please help me in creating a MTD for this one?I know i can use the traffic logic, but i am not sure how to embed the current one to that one.

If you can help me with these 2 it would be really great.

Once again thanks a lot for your help.

Thanks