Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

dilipmantu
New Contributor

Expression related problem

Dear all,
I am working with a problem:

There are three list boxes to select the financial year range, quarter and months respectively in our application
each of which can serve as a filter.

We are trying to make a report showing the customer-wise total sales of the previous financial year(first measure column),
that of the current financial year(second measure column), the corresponding indicator (next column) and the corresponding percentage.

The indicator(for each rows in the report) will be calculated as:
if the total sales of the current year is greater than that of the previous then 'Growth',
if it's less the 'De-growth' and if it's equal then 'No-growth'.

The percentage(for each rows in the report) will be calculated as
For e.g. 'Growth': (Total number of rows with indicator='Growth'/Total number of rows displayed in the report)*100.
The same logic will be followed for the other two indicators also.

We need to create another summary report based on the outcome of the above report considering the indicators as the
dimension and their percentage values and total values.
Thus this report will contain three columns:
1. Indicator: showing one of the aforesaid value (i.e. 'Growth'/'De-growth'/'No-growth')
2. Corresponding percentage value.
3. Corresponding total values.

The logic for calculating the total value for each indicator will be as:

For 'Growth': The sum total of all the (Total sale value or previous year-That of the current year)
for which indicator='Growth' in the first report. The same logic will be followed for the other
indicators too in order to get the corresponding total value.

This is our urgent requirement.

We are attaching the application herewith.
The first report heading is given as the "Main Report" and the next one as "Summary Report".

Please do reply if you need any more information.

Thanks in advance.

Best regards,

Dilip

7 Replies
Not applicable

Re: Expression related problem

Hi there dilipmantu,

The solution to your problem is to calculate your indicator on the script(or possibly on the sql query by using sum(2012)-sum(2013) )and then

you could easily create your prefered table.

Regards

dilipmantu
New Contributor

Re: Expression related problem

Hi nikolaos,

I need soluation in expression label.

Regards

Dilip

Not applicable

Re: Expression related problem

Hi dilipmantu,

I dont think it is possible to do that in expression label with the current form of your model.

Regards

lironbaram
Honored Contributor II

Re: Expression related problem

hi

attach is what you asked for

hope it helps you

dilipmantu
New Contributor

Re: Expression related problem

Hi Baram,

Thank you very much for looking into our problem and sending the application. The "Summary Report" is almost OK.

However a little bit more effort is required. Please address the problem in the "Main Report" first where the percentage values for each indicator ('Growth'/'Degrowth'/'Nogrowth' depending on the values of the first and second measure columns) need to be displayed. Naturally these values will repeat many times. Once this is done then the next report (Summary) can be done based on it where the indicators will act as dimension.

Please note some points:

1. The inline data for "S" in the script is given for example only. In reality there will not be such thing. Those percentage values will be obtained on-the-fly from the first report (i.e. with the caption: 'Main Report').

2. While calculating the percentage value, please don't divide by "count(DISTINCT Customer)" which is a static value. It should be the total number of displayed customer records in the "Main Report".

Please do reply us if you need any more information regarding this.

Again thanks a lot for your time.

Best regards,

Dilipmantu

dilipmantu
New Contributor

Re: Expression related problem

Hi Baram,

We are waiting for your reply till now.

Please look into this.

Thanks and regards,

Dilipmantu

dilipmantu
New Contributor

Re: Expression related problem

Hi Baram,

We are waiting for your reply till now.

Please look into this.

Thanks and regards,

Dilip Dolui

Community Browser