topic Chart Evolution - According to the selected Period in QlikView

Chart Evolution - According to the selected Period

Sat, 16 Jan 2016 23:51:47 GMT

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!

Re: Chart Evolution - According to the selected Period

marcus_sommer — Sun, 17 Jan 2016 15:05:12 GMT

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

Re: Chart Evolution - According to the selected Period

Sun, 17 Jan 2016 15:37:02 GMT

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

Re: Chart Evolution - According to the selected Period

sunny_talwar — Sun, 17 Jan 2016 17:24:47 GMT

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

Re: Chart Evolution - According to the selected Period

Sun, 17 Jan 2016 17:42:13 GMT

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)

Re: Chart Evolution - According to the selected Period

sunny_talwar — Sun, 17 Jan 2016 18:53:02 GMT

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?

Re: Chart Evolution - According to the selected Period

Sun, 17 Jan 2016 19:10:58 GMT

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

Re: Chart Evolution - According to the selected Period

sunny_talwar — Sun, 17 Jan 2016 19:11:49 GMT

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

Re: Chart Evolution - According to the selected Period

Sun, 17 Jan 2016 19:18:34 GMT

Thanks Sunny. I will analyse your QVW