Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Qlikkers,
In the attachment you find a list with 0's and 1's of a customer (Cust). I'm facing difficulties with calculating the amount of 1's of the new period versus the amount of 1's of the previous period.
So in every period I need to count all the new 1's. In the rows 29 till 31 of the Excel file I'm summing up the amount of the last created period (the column far right, row 29 in Excel) and I take the difference of the other columns with the total of the columns of the previous period (row 30).
Below I've added my main formulas, which have worked until the start of the new year:
sum([P1]) AS Per1,
sum([P2]) AS Per2,
. . .
. . .
. . .
sum([P12]) AS Per12
sum([P1-2018]) AS Per13
Group by Cust,Period,Year;
Totals:
NoConcatenate
LOAD *,
If(Cust=Previous(Cust),Previous(Totaal),0) As TotalPP;
LOAD *,
(P1+P2+P3+P4+P5+P6+P7+P8+P9+P10+P11+P12) AS Total
Order by Cust,Period;
I guess the problem is the fact that I don't work with a Master Calendar this time and therefore Qlik doesn't read P1-2018 as the successor of P12 (I've tried it with P12-2017 already, but no luck)? Can you help me with this problem? I can see a Master Calendar would be more convenient. Since I only have periods instead of exact dates, can we solve this without using this calendar? It seems like an easy problem, but I can't make it work. Thanks a lot in advance!
Regards,
Pim
I don't really understand your data to well to recommend anything... you have 4 similar looking tables on Data17 tab. What are they showing? Does this mean that you have a different file for each period? or how is the actual data laid out? It would be much easier to give suggestions if you can provide a sample file where the data is already loaded, or in a form which can be easily loaded.
Hi Sunny,
In attached Excel file you see the tab New, which presents the latest downloaded table of the current period. When we are a period further, this table will be added to the table in tab Old. Only the most recent table will be shown in tab New and tab Old is a concatenated table that consists of all the older tables. So like you said, we have a different file for each period and these will be added to the table in tab Old.
Just like in my data the output of P1-2018 and P2-2017 isn't aligned with the desired output in QV. I hope this suffice!
Regards,
Pim
I was able to get all the periods of 2017 to work... but not sure why 2018, period = 1 is 13 and not 28?
I think you're adding the difference between the sum of P1-2017,.....,P12-2017 from the old table (Period =12) and from the new table (Period =1, difference is 5) to value of P1-2017 (Sum is 23) instead of the value of P1-2018 (Sum is 8). See below screenshot.