Calculation - Sum and difference in Script based on condition

[Narz]

Hi all,

I have three excel input - One is currentdata.xls, New.xls, old.xls. These are monthly data. 

The input fields are the same, hence I have concatenated and added Flag to differentiate. 

1. Load column1, column2, column 3, Month, 'Current' as Flag from currentdata.xls

2. Load column1, column2, column 3, Month, 'New' as Flag from New.xls

3. Load column1, column2, column 3, Month, 'old' as Flag from old.xls

Now I wanted to do calculation and store in QVD for future calculations.

For month = Jan, I need the below calculation,

 a. if (Flag='Current', count(column1)) + if(Flag = 'New', count(column1)) as result1

b. result1 - if(Flag='old', count(column1) as result2

c. result2 for month Jan is my column1 for Feb.

d. This applies for each month.

When I do the calculation in front end or chart I am not able to store the result to input for the next month. So i would like to make the calculation in script and store the Month wise result in qvd to get for the next month. Can you please suggest a solution for the above. TIA

[Nicolae_Alecu]

Hello,

Try this:

count(if(Flag='Current',Col1)) + count(if(Flag='New',Col1)) as Result1,
count(if(Flag='Current',Col1)) + count(if(Flag='New',Col1)) - count(if(Flag='Old',Col1)) as Result2

[Narz]

Hi @Nicolae_Alecu 

This works for one month. Can you suggest me a way to get Result2 being the current value for the next month, I can achieve this in chart. Though I need this as a continuous value.

The flow,

1. For Jan - Current + New - Old = Result2.

2. The Result2 serve as current value for Feb.

3. Result2 + Feb New  - Feb Old = FebResult

4. FebResult will be the current value for Mar.

5. FebResult + MarNew - MarOld = MarResult

and so on for all the other months. 

Is it possible to achieve this in Script. Since in Chart I will not be able to store data monthwise.

Please pour in your suggestion. Thanks

[marcus_sommer]

It sounds that your data are crosstables - in this case you should transform them to normal data, see:

The Crosstable Load - Qlik Community - 1468083 

[Narz]

Hi my data is not cross table. It is straight table. Three Excel inputs 

1. Current - only available for the month January

2. Old and New Excel I get each month.

Feb Current I have to calculate from Jan Current + Jan New - Jan Old.

Is there a way I can store these monthly calculations to be used for the next month. 

count(if(Flag='Current',Col1)) + count(if(Flag='New',Col1)) as Result1,
count(if(Flag='Current',Col1)) + count(if(Flag='New',Col1)) - count(if(Flag='Old',Col1)) as Result2

The above works fine for Jan month still Feb calculation comes wrong as Feb has to use Result2 as its current value.

[marcus_sommer]

I think that if you want to have a different calculation for the different months you will need to include this as condition within the calculation, like:

if(Month = CurrentMonth, Expr1, Expr2) as Result

which may also save the struggles later with several result-fields.

[Narz]

My current expression is like this,

1. For Jan - I have direct data,

2. Feb - > calculate based on Jan values

3. Mar -> Calculate Jan + Calculate Feb Value

if(Month='JAN', Count({<status={'Current'}>}[column1]), 

if(GetFieldSelections(Month) = 'FEB',

    Count({<Month={"$(=Month(AddMonths(Date#(Month,'MMM'), -1)))"},status={'Current'}>}column1) 

+

Count({<Month={"$(=Month(AddMonths(Date#(Month,'MMM'), -1)))"},status={'New'}>}column1) 

-

Count({<Month={"$(=Month(AddMonths(Date#(Month,'MMM'), -1)))"},status={'Old'}>}column1)

, Count({<Month={"$(=Month(AddMonths(Date#(Month,'MMM'), -2)))"},status={'Current'}>}column1) 

    +

Count({<Month={"$(=Month(AddMonths(Date#(Month,'MMM'), -2)))"},status={'New'}>}column1) 

-

Count({<Month={"$(=Month(AddMonths(Date#(Month,'MMM'), -2)))"},status={'Old'}>}column1)

        +

        Count({<Month={"$(=Month(AddMonths(Date#(Month,'MMM'), -1)))"},status={'Current'}>}column1) 

    +

Count({<Month={"$(=Month(AddMonths(Date#(Month,'MMM'), -1)))"},status={'New'}>}column1) 

-

Count({<Month={"$(=Month(AddMonths(Date#(Month,'MMM'), -1)))"},status={'Old'}>}column1)

    )

)

4. If I have to do for 12 months I have to keep adding the condition.

Is there a way to get this simplified?

[marcus_sommer]

My hint above was aimed to do the essential work within the data-model and not within the UI.

Your approach might be simplified by using a numeric period-field and shortened by transferring some calculation-parts within parameterized variables and maybe including there any logic to react to the number of the included periods - and maybe further by skipping the manual accumulation-approach with the use of interrecord-functions like above().

In more details it means - the numeric period field may look like:

year(Date) * 12 + month(Date) as P

which enables an assigning of periods with P - n even by any year-switch and also to apply >= and <= conditions instead of only = against a string-representation.

A parameterized variable might be:

v: count({< P = {$1}, status = {'$2'}>} column1)

and the call of it for example:

$(v($(=max(P)-1), old))

Further an accumulation within a chart could be done with something like:

rangesum(above(MyExpression, 0, rowno()))

But even if you could implement all of such approaches successfully your calculation would be further quite long and complex. And this for a basis-expression. If it comes to multi-level aggregations to compare the results against previous periods or against budgets and/or calculating any forecast and/or calculating any kind of rates - you would need to include TOTAL and/or more conditions and/or any aggr-wrapping - it would become a nightmare ...

Therefore I suggest to rethink the entire approach and looking at first if any classical incremental method is applicable and if not to implement any file- and period-overlapping categorizing / flagging of the data. 

[Narz]

I tried initially in data model, like calculate my final value for Jan,

Current + new - Old = Result.

This is tied with Month field Jan and hence I couldn't assign or pass the January Result to the Current value of Feb. If I could do this, consecutively, I can pass the previous month's calcualted Result to the succeding month and proceed to create the visual as required. 

[marcus_sommer]

I wouldn't discard the common logic too easily: end-balance = (new) start-balance and then only considering the +- movements in each period.

I could imagine that this would be sufficient for many views. And if not a lot of additional measurements are thinkable, like: adding an offset-flag to the current period/year and/or adding min/max periods or counting ID's and/or associating them with a The As-Of Table - Qlik Community - 1466130 or applying accumulations with peek() and previous() or ...