Chart Evolution - According to the selected Period

[#1]

I’ve the following QVDs:

- DIMENSION CALENDAR:

               - DATE

               - YEAR

               - MONTH

               - DAY

- FACT TABLES SALES

               - SALES_ID

               - DATE

               - SALES_TYPE

               - SALES_PROFIT

What I am trying to achieve:

• When I select the field Month show the monthly evolution of Profit until the Month selected
• When I select the field Day show the daily evolution (of the Month selected) until the Day select

I’m using the following code to try:

• In Dimension:

=IF(len(trim(GetFieldSelections(MONTH))) > 0, IF(DATE<= date(now()), DAY),
IF(len(trim(GetFieldSelections(DAY))) > 0, IF(DAY(DATE)<= DAY, DAY)))))

• In Expression:

=IF(len(trim(GetFieldSelections(DAY))) > 0, SUM({<SALES_TYPE ={DAD},DATE={"<=$(=vDay)"}, DAY=>} SALES_PROFIT))

How I can realize this?

Thanks!

[marcus_sommer]

Have you tried it without those calculated dimensions and conditions within the expressions by using simply a cyclic dimension-group (including month and day) and an expression like sum(SALES_PROFIT) and then selecting the wanted periods per listboxes/multibox?

- Marcus

[#3]

But with that I've this situation:

When I select the Field Day I obtain a chart only with the Day selected, and I want the evolution until that way

[sunny_talwar]

Are you looking for something like this?

1) If Month is selected (Day is not selected) - You will see YearToMonth

Expression: =Sum({<DATE = {"$(='>=' & Date(YearStart(Max(DATE))) & '<=' & Date(Max(DATE)))"}, MONTH, YEAR, DAY>} SALES_PROFIT)

2) If Day is selected (Month is not selected): You will see MonthToDate

Expression: =Sum({<DATE = {"$(='>=' & Date(MonthStart(Max(DATE))) & '<=' & Date(Max(DATE)))"}, MONTH, YEAR, DAY>} SALES_PROFIT)

Dimension and Expressions are both conditional.

Dim1: MONTHYEAR with condition =GetSelectedCount(MONTH) >= 1

Dim2: DATE with condition =GetSelectedCount(DAY) >= 1

Similarly the two expressions are also conditional based on the two conditions.

Now the question is what do you see when neither MONTH or DAY are selected? Additional What would be the output when both MONTH and DAY are selected?

Currently the behavior in above two cases might not be the one you would like, but it can be tweaked based on your requirements.

HTH

Best,

Sunny

[#5]

Hi Sunny, many thanks for your response

This is the situation:

If I have selected the Year, I will show the montlhy evolution of that year

If I have the Quarter I will show the evolution of the months in that Quarter

If I have the Month Selected I will show all of the day of that month

If I have selected the Day , I will show the evolution until que day in the selected month .

Basically we can think like a logical demonstration , where selected

• 1º year - where we show the monthly evolution
  • 2ºQuarter - Where we show the evolution of every month presents on that Quarter
    • 3ºMonth - Where we show the evolution of days in that month
      • 4ºDay - Where we visualize the daily evolution until the day selected (in the month selected)

[sunny_talwar]

Is that step wise selection you will make or would you be making just have one selection at a time? I mean would you only have MONTH or YEAR or QUARTER or DAY selected at one point of time or can you have YEAR and MONTH or any combination of those fields selected by the users?

[#7]

No, Imagine that:
When I've the Day selected, I already have been select Year -> Quarter -> Month

When I've the Month selected,I already have been select Year-> Quarter

When I've the Quarter selected, I already have been select Year.

Is possible to do it?

Thank so much for you help Sunny

[sunny_talwar]

Tried to make some tweaks, see if this is closer to what you want.

[#9]

Thanks Sunny. I will analyse your QVW